Using Countifs to ONLY COUNT numbers NOT FORMULAS

I have been trying for an hour to figure out how to get a formula that counts only if the cell contains a number. I don't want it to count a cell if it contains text or a formula. If it counts a cell that contains text, it counts a descriptive header that I don't need want counted. If it counts a cell that contains a formula, it double counts something. Is it possible to do this? If needed I can post the many, many failed formulae that I have tried to make this work.


As you can see, I only want to count the people that have a positive number in column C. If I count C7 I count Joey Joe Joe twice. If I count C1, I count something that I don't want counted!

Thanks in advance.

Do you mean COUNT or SUM? You might like to look at COUNTIF or SUMIF, i.e.:

=SUMIF(A:A,"Joey Joe Joe",C:C)

Hope this helps.

Pete

No. I only want to get a count of people that have a positive number in the C column. If I count the cells that start with total, I will get a double count.

Try this:

=COUNTIFS(A:A,"< >Total*",C:C,">0")

I assume from your screen shots that you are NOT using XL2003 - please update your profile.

Hope this helps.

Pete

I put another copy of what I am trying to do below. In the example below, I don't want to count Chalmers and Skinner, but I do want to count Jebidiah, Joey and Jr. The count I am looking for is 3 because those 3 people have positive numbers in the c column.

I cut and pasted that formula and got 0. Did I do it wrong? And I will update my profile now.

The numbers in column C are aligned to the left in your screen shots, but the normal alignment for numbers is to the right - could they be text values that just look like numbers?

Pete

I tried that formula and got 0. Did I enter it right?

I made sure they were numbers and didn't get a different answer.

Also, in both cases (posts #10 and #12), you should ensure that the formula is placed in a cell which is outside the ranges used in the formula (in my case, I assumed that you would put it somewhere in column D, or to the right of that).

Pete

Good call on putting the cells outside of the range of my formula. I put it outside the range for #12 and it counted all the cells that had a value in it and the cells that contained a formula. I'm getting closer, because this time I got a six, but I'm still not able to get the number I'm looking for.

Thanks for that tip. Here's the workbook

Or, given that you put my formula in A20, you can amend it to this:

=COUNTIFS(A1:A19,"<>Total*",C1:C19,">0")

Hope this helps.

Pete

Y'all are great. I put both of your formulas into my spreadsheet and they both give me the same answer on my actual spreadsheet. Pete_UK, I appreciate you not calling me an idiot even though I made a pretty idiotic mistake and Tony Valko, thanks for your formula! I really think I might not have EVER figured this out.