Twist on a "SUMIF" formula in Excel 1

[RyanScharfy]

I have a file with two worksheets, "Sheet1" and "Sheet2".

To solve my problem here's a simplified example. Sheet1 looks like this:

Column A Column B Column C

Blue 1 100
Blue 2 100
Red 1 100
Blue 1 100

In Sheet2, I'd like to put in a formula that gives me the summed amounts from column C of the rows where "Blue" is in column A and "1" is in column B. The total in this example would be 200. I know of using "SumIf" but only for one condition, Blue or 1, but not both.

My particular real world document has thousands of rows, so a simple sum formula is not practical. Thanks for any help.

 

[firefytr]

Hi there,

You can use something like this ...

=SUMPRODUCT((Sheet1!A2:A5000="Blue")*(Sheet1!B2:B5000=1),Sheet1!C2:C5000)

Change the 5000 row to the last row of your data (can be a dynamic named range if desired). Also, if you would prefer, change out the "Blue" and 1 to cell references where you can enter these values.

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[RyanScharfy]

Thanks Zack,

Worked like a charm.