Hi ,
It looks like you're using an ad blocker.



The revenue generated from the adverts on the site is a critical part of our funding - and it's because of these ads that I can offer the site for free. But using the site for free AND blocking the ads doesn't feel like a great thing to do, which is why this box is so large and inconvenient. Some sites will completely block your access, but I'm not doing that - I'm appealing to your good nature instead. Did you know that you can allow ads for specific sites, whilst still blocking them on others?

Thanks,
Ian Williams aka Fetch

F'Laps - Saturday 5th July

Join in with our challenge this Saturday. Run a set distance every hour until you can't run any more!
or for an ad-free Fetcheveryone experience!

SpreadEveryone: The Fetchland Excel wire

102 watchers
um
um
Nov 2019
12:16pm, 28 Nov 2019
1,435 posts
  • Quote
  • Pin
um
Well spotted SPR,

=SUMPRODUCT(--EXACT(A:A,"London")) seems to work just as well !
GregP
GregP
Nov 2019
12:41pm, 28 Nov 2019
116,415 posts
  • Quote
  • Pin
GregP
The brackets turn out to be because of an array function not wisible to the naked eye. I did the evil CSE trick and all is well. Works fine but will be a bastard for whoever inherits it from me
SPR
SPR
Nov 2019
12:46pm, 28 Nov 2019
29,724 posts
  • Quote
  • Pin
SPR
Why not just do the brackets instead of CSE when you don't have to?
GregP
GregP
Nov 2019
12:48pm, 28 Nov 2019
116,417 posts
  • Quote
  • Pin
GregP
Do I have that option? Here's a cut and paste which (probably) hides a CSE array
GregP
GregP
Nov 2019
12:48pm, 28 Nov 2019
116,418 posts
  • Quote
  • Pin
GregP
=SUMPRODUCT((--EXACT(V$2:V$63,$M69)))+(0.1*(SUMPRODUCT((--EXACT(V$2:V$63,LOWER($M69))))))
SPR
SPR
Nov 2019
1:21pm, 28 Nov 2019
29,725 posts
  • Quote
  • Pin
SPR
I know you generally don't need CSE with SUMPRODUCT, but not sure how to change yours so you don't ATM.
SPR
SPR
Nov 2019
1:28pm, 28 Nov 2019
29,726 posts
  • Quote
  • Pin
SPR
What's the 0.1 about?
larkim
larkim
Nov 2019
1:39pm, 28 Nov 2019
9,609 posts
  • Quote
  • Pin
larkim
Beyond "it doesn't work otherwise", is there a simple guide to how an array formula produces its output differently to a non-array formula?

I've used them from time to time before because various good internet sites have told me I need to. But never really understood "why" other than "just because".
GlennR
GlennR
Nov 2019
2:18pm, 28 Nov 2019
46,591 posts
  • Quote
  • Pin
GlennR
You can build a condition into an array formula that you can’t include in a non-array version.

SUMPRODUCT was supposed to eliminate the need for assay formulae.
SPR
SPR
Nov 2019
2:19pm, 28 Nov 2019
29,727 posts
  • Quote
  • Pin
SPR
They essentially make a list in the background which can be analysed.

excel-easy.com

About This Thread

Maintained by John66
This is bugging me, I'm sure it should be much easier

A1 has 25/6/2010
B1 has 24/6/2013

So t...
  • Show full description...

Related Threads

  • excel
  • support
  • tech
  • work

Report This Content

You can report any content you believe to be unsafe. Please let me know why you believe this content is unsafe by choosing a category below.



Thank you for your report. The content will be assessed as soon as possible.










Back To Top

Tag A User

To tag a user, start typing their name here:
X

Free training & racing tools for runners, cyclists, swimmers & walkers.

Fetcheveryone lets you analyse your training, find races, plot routes, chat in our forum, get advice, play games - and more! Nothing is behind a paywall, and it'll stay that way thanks to our awesome community!
Get Started
Click here to join 114,481 Fetchies!
Already a Fetchie? Sign in here