Excel Sumproduct help ( Count blank cell as well)

[Mallesh23]

Hi Team

Need your help in Sumproduct formula.
In Column J I want to include two Criteria. {"Pending" and blank Cells), How to include blank cell as well.

=SUMPRODUCT(ISNUMBER(MATCH($H$2:$H$31,{"India","USA","Canada"},0))
*ISNUMBER(MATCH($I$2:$I$31,"20",0)))
*ISNUMBER(MATCH($J$2:$J$31,{"Pending",""<>""&"*"},0)) Here in this line I am trying to include Pending or blank cells.


Thanks
mg

 

[JoeMo]

Maybe:
=SUMPRODUCT((H1:H31={"India","USA","Canada"})*(I1:I31=20)*((J1:J31="Pending")+(J1:J31="")))

 

[Mallesh23]

Hi JoeMo,

Thank you so much, above formula is working as expected. Thanks.

 

[Mallesh23]

Hi JoeMo,

I liked this formula, While practising this formula I have below questions.

Multiple columns with multiple Criteria....... like. below. not works
=SUMPRODUCT(($H$1:$H$31={"India","USA","Canada"})+($I$1:$I$31={10,20,60}))


Multile Columns with multiple Criteria (excluding few Criteria) not working
=SUMPRODUCT(($H$1:$H$31<>{"India","USA","Canada"})*($I$1:$I$31={10,20,60}))

Can sumproduct formula support WildCard Charecter. here its not working.
=SUMPRODUCT(($H$1:$H$31={"India","USA","Canada"})*($I$1:$I$31={60})*($J$1:$J$31="Com*"))


Thanks for your help in advance!


Regards,
mg

 

[JoeMo]

Hi JoeMo,

Thank you so much, above formula is working as expected. Thanks.

Hi JoeMo,

I liked this formula, While practising this formula I have below questions.

Multiple columns with multiple Criteria....... like. below. not works
=SUMPRODUCT(($H$1:$H$31={"India","USA","Canada"})+($I$1:$I$31={10,20,60}))


Multile Columns with multiple Criteria (excluding few Criteria) not working
=SUMPRODUCT(($H$1:$H$31<>{"India","USA","Canada"})*($I$1:$I$31={10,20,60}))

Can sumproduct formula support WildCard Charecter. here its not working.
=SUMPRODUCT(($H$1:$H$31={"India","USA","Canada"})*($I$1:$I$31={60})*($J$1:$J$31="Com*"))


Thanks for your help in advance!


Regards,
mg

You are welcome - thanks for the reply. As for your additional questions: the first two formulas work fine for me. What exactly doesn't work for you? For the third formula, you are correct that SUMPRODUCT doesn't support wildcards, but see this link for ways to overcome this limitation: Article 12 - Overcoming Wildcard Limitation in Sumproduct

 

[Mallesh23]

Hi Joemo,

Thanks once again for your help !, I am getting above two formulas result. However I am not getting correct result. when I filter the data manually and Cross checked it.
I don't know how the formula is calculating the values.

=SUMPRODUCT(($H$1:$H$31={"India","USA","Canada"})+($I$1:$I$31={10,20,60})) Result through formula 35 Actually it should be 12

=SUMPRODUCT(($H$1:$H$31<>{"India","USA","Canada"})*($I$1:$I$31={10,20,60})) Result through formula 15 Actually it should be 07

if you explain how this formula works. it will be fine for me.

Thanks in advance for you help

Regards,
mg

 

[JoeMo]

The first formula will return 1 for each instance of anyone of the three countries found in col H plus 1 for each instance of 10 or 20 or 60 found in col I.

The second formula will return 1 for each entry in col H that is NOT one of the three countries AND has a value of 10, 20 or 60 in the companion cell in col I.

In other words the multiplier character (*) should be read as AND while the + character should be read as OR.

If you can't rationalize that with your data, post the data using XL2BB (XL2BB - Excel Range to BBCode) and I will try to help you further.

 

[Mallesh23]

Hi JoeMo,

I need one more help in below Situation . I have 3 Columns and three Array Criteria, First Two criteria in sumproducts array works.
When I tried to Include third Column Range and its Criteria its not working.
Third Column here is J column, Array Criteria is either Completed or Blank Cells.

=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))) ' Here How to include blank cells as well {"Completed",""""}

Thanks for your help in advance !!

Thanks
mg

 

[JoeMo]

Look at the examples in post #6 and add the J column criteria as an OR by using the + sign.

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

Note that you don't need to use ISNUMBER(MATCH .....)) at all. Using them will just add calculation time and slow your calculations down.