Count with multiple criteria in multiple cells

  • Thread starter Thread starter MadProphet
  • Start date Start date
M

MadProphet

I am trying to get a count of how many rows have a value of "1" in the F
column AND have a value of "0 PAPER" in the D column. I keep getting an error.

I am using Excel 2003 and the following formula
=SUMIF('CURRENT EDIT STATUS'!D2:D3565,"0 PAPER", 'CURRENT EDIT
STATUS'!F2:F3565)
 
You can use Countifs if you have XL 2007. If not, use Sumproduct, as in:

=sumproduct(--('CURRENT EDIT STATUS'!F2:F3565=1),--('CURRENT EDIT
STATUS'!D2:D3565="0 PAPER"))

Regards,
Fred
 
Try this

=SUMPRODUCT((D2:D3565="0 Paper")*(F2:F3565=1))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
oops,

your referring to another worksheet

=SUMPRODUCT(('CURRENT EDIT STATUS'!D2:D3565="0 Paper")*('CURRENT EDIT
STATUS'!F2:F3565=1))


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
I was able to follow your logic :) I assume I can add other conditions by
adding more parentheticals to the multiplication string e.g.

=SUMPRODUCT(('CURRENT EDIT STATUS'!D2:D3565="0 Paper")*('CURRENT EDIT
STATUS'!C2:C3565="E ENCOUNTER")*('CURRENT EDIT STATUS'!F2:F3565=1))

I tried it in the sheet and it seems to work. Thanks for your help.
 
Back
Top