Sumifs wild card dealing with blamks

Hi all,
I have searched and searched to try and resolve this problem.
I am trying to create a series of sumifs formulas that the criteria is set upon the result of a number of drop down selections.
Now the issue I have is some of the ranges have numbers and some will have blanks.

I have attached a sample work book.
I have got over the issue of numbers by changing the numbers to text ie 1 is now one

But the problem I have is say column A has either Y, N or blanks.
I may wish to sumifs if either Y or N - no problem.
But what if I want to select ALL
using * will still only sum those with Y or N in the column but not ""
I only want the 1 formula and not 2 ie: sumif ">" + sumif ""

any suggestions?

Hi,

Perhaps:

=SUMPRODUCT(SUMIFS(C4:C9,A4:A9,IF(A2="All",{"*",""},A2),B4:B9,IF(B2="All",{"*";""},B2)))

Regards

That worked really well.
I just added another range and lookup value and it doesn't work
not sure why?
=SUMPRODUCT(SUMIFS(E4:E9,A4:A9,IF(A2="All",{"*",""},A2),B4:B9,IF(B2="All",{"*";""},B2),C4:C9,IF(C2="All",{"*";""},C2)))

Hi All,

perhaps semicolons are not the right delimiters

=SUMPRODUCT(SUMIFS(E4:E9,A4:A9,IF(A2="All",{"*",""},A2),B4:B9,IF(B2="All",{"*";""},B2),C4:C9,IF(C2="All",{"*";""},C2)))

Hope it helps

tried this
=SUMPRODUCT(SUMIFS(E4:E9,A4:A9,IF(A2="All",{"*",""},A2),B4:B9,IF(B2="All",{"*",""},B2),C4:C9,IF(C2="All",{"*",""},C2)))

it only sums the columns where there is a value in A B & C

Ah. I should've mentioned that that construction is limited to two ranges with criteria (for reasons of "dimensionality" - explanation within this post if you're interested).

If you have more, a different approach is needed, e.g. this array formula**:

=SUM(E4:E9*IF(A2="All",1,A4:A9=A2)*IF(B2="All",1,B4:B9=B2)*IF(C2="All",1,C4:C9=C2))

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

that will be why. I tried changing , and ; around but it would only work on one of the ranges at a time.
that array formula will work though.
I have about 18 criteria to select. a few more than the 15 limit of the advanced filter. The other way was to use vba to control the auto filter but then you get a delay between selections which I didn't want

Thanks
I will see how I get on.

how does the 1 work in
IF(A2="All",1,A4:A9=A2)

think I just worked it out

if the value in each cell = criteria then 1, if its "all" then 1
then sum range value is returned for each row where values are all 1
the array sum formula then adds them all up

very clever
this also works for numbers as well which solves my other issue

If you've got many columns to consider then I'd suggest using this version (still an array formula):

=SUM(E4:E9*(MMULT(IF(A2:C2="All",1,0+(A4:C9=A2:C2)),TRANSPOSE(COLUMN(A2:C2)^0))=COLUMNS(A2:C2)))

which, although arguably less intelligible, has the advantage that, by simply amending the column C parts as required (i.e. C2 and C9), can accommodate as many columns' worth as you wish, without the need for separate clauses for each as per the previous formula I gave.

Regards

Thanks, that final one is excellent when you get your head around it.
Although it would probably mean I would need all my criteria selections in the same order as the columns and also one for each column. But with my data I wont be using all the columns and the selection boxes are in various different positions.

I have managed to used the first formula for both a sumif & countif alternative and solves a lot of issues regarding blanks and numbers.

Thanks again

Originally Posted by Sean Thomas

Although it would probably mean I would need all my criteria selections in the same order as the columns and also one for each column. But with my data I wont be using all the columns and the selection boxes are in various different positions.

Yes, you're correct in that assessment, so if that's not a realistic possibility for you then go with the first, longer version.

Glad to help!

Cheers