Excel Sumproduct Count Blank Cell as well...

[malleshg24]

Hi Team,


Need excels sumproduct formula help to Count blank cells as well.
Below are attempted formula and I am comparing the result after manual filtering the Data.


Gives Result 3 , Expected 10
Range("C12")=SUMPRODUCT(($H$1:$H$31={"India","USA","Canada"})
*($I$1:$I$31={10,20,30})
*(($J$1:$J$31="Completed")+($J$1:$J$31="")))


Gives Result 6, Expected 10
Range("C13")=SUMPRODUCT(ISNUMBER(MATCH($H$1:$H$31,{"India","USA","Canada"}, 0))
*ISNUMBER(MATCH($I$1:$I$31,{10,20,30},0))
*ISNUMBER(MATCH($J$1:$J$31,{"Completed"},0))+ISNUMBER(MATCH($J$1:$J$31,{""},0))) it is not Counting Blank




Regards,
mg

[p45cal]

I think, for expedience, you could use:

=SUMPRODUCT(($H$1:$H$31="India")+($H$1:$H$31="USA")+($H$1:$H$31="Canada"),($I$1:$I$31=10)+($I$1:$I$31=20)+($I$1:$I$31=30),($J$1:$J$31="Completed")+($J$1:$J$31=""))

I'm fairly sure that the likes of the start of your formula:
SUMPRODUCT(($H$1:$H$31={"India","USA","Canada"})*($I$1:$I$31={10,20,30}))
will only count instances of India with 10, USA with 20 and Canada with 30, of which there are 4. The one instance of India with 10 has 'Pending' in the next column, so your full formula returns 3.

[malleshg24]

Hi P45Cal,

Thanks for your help and highlighting issue, it worked brilliantly.
So I have to avoid multiple Criteria like these {"India","USA","Canada"} as it will consider first item.

Regards,
mg

[Bob Phillips]

So I have to avoid multiple Criteria like these {"India","USA","Canada"} as it will consider first item.

No, it is not quite as simple as that.

You can test a single array of values quite easily, so this works fine

=SUMPRODUCT(($H$1:$H$31={"India","USA","Canada"})
*(($J$1:$J$31="Completed")+($J$1:$J$31="")))

but when you AND two arrays you get less full matches.

[Bob Phillips]

You can have one array set of conditions and one set of OR conditions like so

=SUMPRODUCT(($H$1:$H$31={"India","USA","Canada"})
*(($I$1:$I$31=10)+($I$1:$I$31=20)+($I$1:$I$31=30))
*(($J$1:$J$31="Completed")+($J$1:$J$31="")))

or

=SUMPRODUCT((($H$1:$H$31="India")+($H$1:$H$31="USA")+($H$1:$H$31="Canada"))
*($I$1:$I$31={10,20,30})
*(($J$1:$J$31="Completed")+($J$1:$J$31="")))