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
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