sumif on multiple non adjacent cells??

[neowok]

basically i have this =SUMIF((B7,B17,B27,B37,B7,B47,F7,F17),">0") which keeps returning #value.

I want to sum each of those cells only if its value is >0. I have tried entering it as an array formula the same as above which I dont fully understand, and it still returns #value

[Domenic]

Try the following...

=SUMPRODUCT(--(N(INDIRECT({"B7","B17","B27","B37","B47","F7","F17"}))>0),N(INDIRECT({"B7","B17","B27","B37","B47","F7","F17"})))

Hope this helps!

[neowok]

ok just trying to get my head around how this works, specifically the >0 section. I understand that it would multiply the 2 arrays together and add the results but im not sure how it only does this when the individual values are >0.

I've seen a site explaining the use of "sumproduct(--" and I know indirect will grab the cell contents and N will convert that to numbers, but its the rest of it I dont understand, why is indirect even needed at all when the cell contents are already numbers? and where does that >0 fit in when theres no if statement?

Thanks

[Domenic]

Assuming that B7, B17, F7, and F17 contain the following values, respectively...

100
-50
-25
15

…and we have the following formula...

=SUMPRODUCT(--(N(INDIRECT({"B7","B17","F7","F17"}))>0),N(INDIRECT({"B7","B17","F7","F17"})))

The first part of SUMPRODUCT:

N(INDIRECT({"B7","B17","F7","F17"}))>0 returns the following array...

{TRUE,FALSE,FALSE,TRUE}

--(N(INDIRECT({"B7","B17","F7","F17"}))>0) returns...

{1,0,0,1}

As you know, the double negative coerces TRUE and FALSE to their numerical equivalent of 1 and 0.

The second part of SUMPRODUCT:

N(INDIRECT({"B7","B17","F7","F17"})) returns

{100,-50,-25,15}

Lastly, when the two arrays are multiplied, the evaluation prior to summing is as follows...

{100,0,0,15}

Here, since the range is non-contiguous, we use an array constant. So instead of having something like A1:A10, we have {...}. But the array constant doesn't accept a cell reference. However, it does accept text, and here is where INDIRECT comes into play. INDIRECT returns a reference specified by a text string, such as "B7". Then we use the N() for de-referencing. It makes available a value specified by a reference.

Hope this helps!

[neowok]

ok thanks, that makes more sence now

its the N(INDIRECT({"B7","B17","F7","F17"}))>0 which seemed strange as I couldnt work out how it could be returning anything for the >0 without an if somewhere but without the if, it obviously returns true/false for each item in the array (rather than a single true/false for the entire contents of the array as a whole).