Need Help with SUMIFS formula

[greykitten]

Hello,
I'm having trouble with a SUMIFS formula where I need to return a dollar value for a range between 0 and 30 days and if the customer type code is either B or E. I've tried using this formula: =SUMIFS($B$3:$B$27,$C$3:$C$27,"0 to 30 Days",$D$3:$D$27,"B")*OR($D$3:$D$27,"E") but it isn't returning any value when I know there should be one.



Can anyone help me? I've searched the Chandoo forum for examples but it's not clicking.

Thank you in advance for your help,
GreyKitten

 

[eibi]

I don't know how to put the OR logic into the SUMIFS formula...is this a suitable workaround?

=SUMIFS($B$3:$B$27,$C$3:$C$27,"0 to 30 Days",$D$3:$D$27,"B")+SUMIFS($B$3:$B$27,$C$3:$C$27,"0 to 30 Days",$D$3:$D$27,"E")

 

[Nebu]

Hi:

Is this what you are looking for.

Thanks

 

[greykitten]

I don't know how to put the OR logic into the SUMIFS formula...is this a suitable workaround?

=SUMIFS($B$3:$B$27,$C$3:$C$27,"0 to 30 Days",$D$3:$D$27,"B")+SUMIFS($B$3:$B$27,$C$3:$C$27,"0 to 30 Days",$D$3:$D$27,"E")

Thank you eibi!

 

[greykitten]

Hi:

Is this what you are looking for.

Thanks Nebu! Very impressive

 

[John Jairo V]

Hi to all!

You can use this formula:
=SUM(SUMIFS(B2:B26,D2:D26,{"B";"E"},C2:C26,"0 To 30 Days"))

Please coment! Blessings!

 

[Khalid NGO]

Hi,
Using John's idea, here is another one with CSE:
=SUM(IF(C2:C26="0 to 30 Days",IF(D2:D26={"B","E"},B2:B26)))

Regards,

 

[greykitten]

Hi,
Using John's idea, here is another one with CSE:
=SUM(IF(C2:C26="0 to 30 Days",IF(D2:D26={"B","E"},B2:B26)))

Regards, Khalid NGO

What is CSE?

 

[greykitten]

Hi to all!

You can use this formula:
=SUM(SUMIFS(B2:B26,D2:D26,{"B";"E"},C2:C26,"0 To 30 Days"))

Please coment! Blessings!

Thank you John, that's great!

 

[greykitten]

Hi,
Using John's idea, here is another one with CSE:
=SUM(IF(C2:C26="0 to 30 Days",IF(D2:D26={"B","E"},B2:B26)))

Regards,

By the way, this summed the entire column which was incorrect. It didn't achieve the goal of filtering by types B and E for items sold from 0 to 30 days.

 

[Khalid NGO]

By the way, this summed the entire column which was incorrect. It didn't achieve the goal of filtering by types B and E for items sold from 0 to 30 days.

Hi Grey,
I did test it before posting.

See the attached with all answers.
Are you sure it is incorrect?