COUNTIF and SUMIF question

G

Guest

I have a column of various dollar amounts and I want to COUNT the number of
values between certain dollar amounts and then SUM the queried values. For
instance, I want to COUNT how many values there are between 0.00 and 4,999.99
and also SUM up what the total values are for that range of dollars. I
tried this formula:

=COUNTIF(A1:A220,">0.00 AND <5000.00")
=SUMIF(A1:A220,">0.00 AND <5000.00")
 
A

Arvi Laanemets

Hi

=COUNTIF(A1:A220,">0")-COUNTIF(A1:A220,"<5000")
or
=SUMPRODUCT(--(A1:A220>0),--(A1:A220<5000))

=SUMIF(A1:A220,">0")-SUMIF(A1:A220,"<5000")
or
=SUMPRODUCT(--(A1:A220>0),--(A1:A220<5000),A1:A220)
 
J

Jason Morin

Count:
=COUNTIF(A1:A10,">0")-COUNTIF(A1:A10,">=5000")

Sum:
=SUM(A1:A10,-SUMIF(A1:A10,{"<=0",">=5000"}))

HTH
Jason
Atlanta, GA
 

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

Similar Threads

SUMIF() to add cells in non-contiguous ranges? (Excel 2003) 4
SUMIF with criteria "<>" & "=" 4
IF/SUMIF function 2
Combine Sumif and Countif 3
Summing up range of data 1
How do I use countif like sumif ie (range,criteria,count_range) 1
Countif & Sumif fx ref data in multiple columns 7
Excel Formula Question 3

Top