Sumif with multiple ranges and multiple criteria

Posted by George Inman on March 08, 2000 4:49 PM

Range 1 Range 2
Invoices Product Line Invoice Amt
64555 PC $1,200
64555 Q $100
64500 Q $45,000
64501 PC $250
64501 PC $80
Sum $46,630

Criteria 1 Criteria 2 Formula Want
64555 PC $1,200
64500 PC $0
64501 PC $330
Sub-total $1,530

64555 Q $100
64500 Q $45,000
64501 Q $0
Sub-total $45,100

Poof $46,630

I would like to use sumif

Posted by judi on March 09, 2000 11:23 AM

If your data is contained in A1:C6 and you put "PC" in cell A10, then here is a formula that should work. Just remember to press cntl+shift+enter when you are done sice it is an array formula.

=SUM(IF($B$2:$B$6=A10,$C$2:$C$6))

If you need other criteria like a subtotal of PCs > 1000 then just insert another if statement after "A10.

HTH,

Judi