SUMIF/COUNTIF with Multiple Arguments??

[Guest]

I've got a data set with two columns that I'd like to
summarize by COUNT and SUM.

eg.
Status Variance
NP $(50,000)
P $ 25,000
P $(10,000)
NP $ 50,000
P $ 35,000
NP $100,000
NP $(25,000)
NP $ 75,000

I want to SUM or COUNT only those records where Status=NP
AND Variance>0. I've tried SUMIF and COUNTIF array
formulas and gone to a couple of Excel sites that have
examples but none of them quite fit this situation. Maybe
I'm not getting the syntax quite right.

Any suggestions?

 

[Ken Wright]

=SUMPRODUCT((RngA="NP")*(RngB>0)) will give you the count

=SUMPRODUCT((RngA="NP")*(RngB>0)*(RngB)) will give you the sum

 

[David]

Great. Thanks so much!

-----Original Message-----
=SUMPRODUCT((RngA="NP")*(RngB>0)) will give you the count

=SUMPRODUCT((RngA="NP")*(RngB>0)*(RngB)) will give you the sum

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

---------------------------------------------------------- ------------------
It's easier to beg forgiveness than ask permission
---------------------------------------------------------- ------------------






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.588 / Virus Database: 372 - Release Date: 13/02/2004


.

 

[Kent]

You can also use an array function like:

{=SUM(IF((A11:A20="NP")*(B11:B20>0),B11:B20))}

Excel Support Technician
www.canhelpyou.com
-----------------------------------------------------------