MS Excel, help with SUMIF criteria?

Question

The data shows the cost and the quantity sold of several items. I need a formula that would count the number of items sold within a range of prices. Right now I have the formula:

=SUMIF(A3:A11,">10.00",B3:B11)

The answer = 24, which is correct.

The criteria (>10.00) sums the items that are greater than $10.00. I need it to count items that are >10.00 AND <22.00. The "AND" is the problem, I don't know how to incorporate it into the criteria. I was thinking an IF, but got stuck.

Thank you very much in advance.

Cost QTY
10.00 2
10.00 3
10.00 25
15.00 10
15.00 2
15.00 2
20.00 2
20.00 3
22.00 5

Answer 1

The link will help

Answer 2

Use:

=SUMIF(A3:A11,">10",B3:B11) - SUMIF(A3:A11,">=22",B3:B11)
This sums all the quantities where the cost is greater than 10 and subtracts all the quantities where the cost is greater than or equal to 22, leaving you with only the quantities where the cost is greater than 10 and less than 22.

or

=SUMIF(A3:A11,"<22",B3:B11) - SUMIF(A3:A11,"<=10",B3:B11)
This sums all the quantities where the cost is less than 22 and subtracts all the quantities where the cost is less than or equal to 10, leaving you with only the quantities where the cost is less than 22 and greater than 10.


If you want to use the solution provided by VBAXLMan, you need to change one thing:
=IF( AND( A3 >10.00, A3 <22.00), 1,0)
should be:
=IF( AND( A3 >10.00, A3 <22.00), B3,0)
This will enter the quantity from the B column (instead of just 1)into the C column if the A column meets your criteria.

Answer 3

SUMIF is a function that works only with one condition
Sorry!
So, I will recommand you to use the alterative easy way, that is adding this function in the next column
=IF( AND( A3 >10.00, A3 <22.00), 1,0)
Then do a regular sum to this new column
=SUM(C:C)

This could be the easiest way, althought there is another way.

mail me here in Y! Answers for anything else related

Enjoy my profile, I am the VBAXLMan