PDA

View Full Version : Excel Sumproduct Count Blank Cell as well...



malleshg24
11-25-2019, 06:39 PM
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
11-26-2019, 04:22 AM
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
11-26-2019, 09:48 PM
Hi P45Cal,

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

Regards,
mg

Bob Phillips
11-27-2019, 09:54 AM
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
11-28-2019, 10:14 AM
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="")))