Summing column based on certain criteria

[KoolKatelyn]

Hello,

I am trying to use a formula that will sum up a column based on certain criteria. I want to sum up column C if the location (column A) is New York and the colors are (column B) Red or Blue.

I attached my work in this thread with the formula that I used, but it does not work.

Any help with this is much appreciated!

[davesexcel]

Possibly you may have to add the SumIfs() together?

Please Login or Register  to view this content.

Somebody may possibly have an elegant way with one formula...

[AliGW]

Or this:

=SUMPRODUCT((A2:A12="New York")*((B2:B12="Red")+(B2:B12="Blue"))*C2:C12)

[oeldere]

With a pivot table.

See the 2 examples in the attached file.

[KoolKatelyn]

I actually figured out another way as well!!

=SUM(SUMIFS($C$2:$C$12,$A$2:$A$12,"=New York",$B$2:$B$12,{"Red","Blue"}))

Thank you all for your responses!!

I actually have another question. How would I do the opposite if I want to sum up the amount with the criteria being New York and the colors cannot be Red or Blue? I don't want to say that the color column is equal to green; I would want it to say <> Red or Blue.

Any help with this is appreciated!!

[davesexcel]

I had tried this your formula as well, I was not getting the correct result.
Edit: I forgot about it not equaling "New York"

[KoolKatelyn]

I had tried this your formula as well, I was not getting the correct result

I got the right result with the formula I tried. I attached my Excel workbook again so you can test it out!

[AliGW]

Try:

=SUMIFS($C$2:$C$12,$A$2:$A$12,"New York",$B$2:$B$12,"<>Red",$B$2:$B$12,"<>Blue")

[KoolKatelyn]

Try:

=SUMIFS($C$2:$C$12,$A$2:$A$12,"New York",$B$2:$B$12,"<>Red",$B$2:$B$12,"<>Blue")

That works perfectly! Thank you so much!

[oeldere]

@KoolKatelyn

Did you also looked ad my pivot table solution in #4?

[KoolKatelyn]

@KoolKatelyn

Did you also looked ad my pivot table solution in #4?

Yes, I saw your pivot table solution which works as well! Thank you for your help!