Count with multiple criteria in multiple cells

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)
 
F

Fred Smith

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
 
M

Mike H

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.
 
M

Mike H

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.
 
M

MadProphet

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top