SUMIF or SUMIFS adding time where criteria is in multiple columns

[66ev]

Hi,

I'm new to excel so would appreciate any help. I want to add Column B if either Column D or E contain "A". I haven't been able to make SUMIFS work, it just returns 0.

Column B is formatted time mm:ss, Columns D and E contain single letters A-H

Formula I've been trying is


=SUMIFS(B6:B60,D6:D60,"A",E6:E60,"A")

Thanks!

[ragavan.sridar1]

Try this..

=SUMPRODUCT((D6:D60="A")* (E6:E60="A")*(B6:B60))

This is an array formula.

[66ev]

Hi Raga,

Thanks for the fast reply!

That almost works... it adds column B if *both* D and E contain "A", but I need it to be if *either* contain "A"

[Tony Valko]

Try one of these:

=SUMPRODUCT(SIGN((D6:D60="A")+(E6:E60="A")),B6:B60)

=SUMPRODUCT(--(LEFT(D6:D60&E6:E60)="A"),B6:B60)

The 1st one is slightly faster to calculate.

[66ev]

Thanks Tony,

I couldn't make your formula work, but I managed to work it out from another support page...

=SUM(IF((D6:D60="A")+(E6:E60="A"),B6:B60,0))

Thanks again for the help!

[Tony Valko]

=SUMPRODUCT(--(LEFT(D6:D60&E6:E60)="A"),B6:B60)

Disregard that one. I wasn't paying attention to the requirements.

I was assuming that the cells would either contain "A" or be blank.

[Tony Valko]

The =SUMPRODUCT(SIGN(... version should work. Works for me.