SUMIFS with multiple criteria and need or to be factored in

[Erin]

I'm trying to incorporate sumifs that adds column when 2 criteria are met and then any of the remaining 3 criteria are met. So it would be "or" instead of "and".

=SUMIFS(email!G6:G51,email!A6:A51,"Y", email!B6:B51,"N" + email!C6:C51,"Y" OR email!D6:D51,"Y"OR email!E6:E51,"Y")I know this formula is wrong but I'm trying to show what I'm trying to do. Essentially, I want excel to sum column G6:G51 in the email worksheet if column A6:A51 is "Y" and column B:B51 is "N" and columns C OR D OR E is "Y". Please help!

 

[Debraj]

Hi Erin,


Welcome to the Forum..


your query can easily solved with normal Boolean Login, where (*) is worked as AND, and (+) is worked as OR, and..

in place of SUMIFS.. you can use SUMPRODUCT.. which is more versatile and robust..

=SUMPRODUCT(($A$6:$A$51="Y")*($B$6:$B$51="N")*($C$6:$C$51="Y")+($D$6:$D$51="y")+($E$6:$E$51="Y"),$G$6:$G$51)

Hope it help..


Please Let us know if you need (SUMIFS + SUMIFS) or {Sum(IF..)} Formula Only..


Regards,

Deb