CountIF Forumla

I have a question which asks: Count the number of products in cells F8 to F18 which is greater than 1200.

Please note that Products are in Column E and Sales are in column F. It will not show up aligned like that on my example.

E F

7 Product Sales
8 Flowers 1500
9 Chairs 1100
10 tables 1000
11 Monitors 900
12 Q1 Sales 4500
13 Flowers 700
14 Chairs 1300
15 tables 1100
16 Monitors 800
17 Q2 Sales 3900
18 Grand Total 8400

For this question i have an answer but i'm not sure if its correct:

=COUNTIFS(F8:F11,">1200")+COUNTIFS(F13:F16,">1200")

This gives the answer of 2 which is correct because 2 of the products Flowers Q1 and Chairs Q2 are over 1200.

Even though this gives the correct answer i'm not sure if this is the correct way to use the formula.

I tried using the COUNTIFS way by putting in the two critea range of F8:11 AND F13:F16 and then using the Criteria as ''>1200''

=COUNTIFS(E8:F11,">1200",F13:F16,">1200")

This gives the answer of '0' what am i doing wrong? the theory works out in my head but it doesnt seem to correct.

I have done easier examples in the past but what i find difficult in this formula is that you have to omit data from Q1, Q2 and Grand total sales to count just the products.

I would really appreciate any help on this? i'm pretty new to using formulas and if any could point me in the right direction that would be great.


Thanks
Neil.

I don't have Excel 2007 (which has the COUNTIFS function) but I believe, it will only count if it meets all of your criteria. Do you need to know by quarter or can you simply use the formula

Please Login or Register  to view this content.

?

ChemistB

Originally Posted by ChemistB

I don't have Excel 2007 (which has the COUNTIFS function) but I believe, it will only count if it meets all of your criteria. Do you need to know by quarter or can you simply use the formula

Please Login or Register  to view this content.

?

ChemistB

I cannot use that formula as this will count the quarterly totals as well, I only want to count products.

Originally Posted by NBVC

Here's another way:

=SUM(COUNTIF(INDIRECT({"F8:F11","F13:F16"}),">1200"))

but maybe to complicated for only 2 ranges....

this may be easier...

=SUM(COUNTIF(F8:F11,">1200"),COUNTIF(F13:F16,">1200"))

Thanks for your help that formula works perfect. I was wondering is there a way to do the formula with using the insert function button, instead of just typing in the formula manually. I don't think i will remember the whole formula for an exam.

This works too but is a bit more complex if you're not familiar with SUMPRODUCT (and definitely requires manual typing);

Please Login or Register  to view this content.

ChemistB