Excel 2007 : Count If function not working correctly

**sir yes sir** · 02-10-2011, 12:23 AM

I have entered a Count If function with two criteria on my spreadsheet, yet it is not counting based on my criteria correctly.

Here are some things you may need to know:
My data that is being counted is in a table format. When setting up the formula and selecting the data in a certain column to be counted (using the ctrl and shift keys) it named my range like so: =COUNTIFS(Table1[[#All],[WP]],"< .5",Table1[[#All],[AS status]],"Starter").

It is only counting 21 instances, yet when I sort the data to "<.5" and "Starter" myself, it shows 22 instances.

I have other COUNTIFS counting in the same range, but different criteria, and they are working fine.

Here they are:
=COUNTIFS(Table1[[#All],[WP]],"< .5",Table1[[#All],[AS status]],"Reserve")
=COUNTIFS(Table1[[#All],[WP]],"< .5",Table1[[#All],[AS status]],"Commish")

**DonkeyOte** · 02-10-2011, 03:36 AM

To best describe or illustrate your problem you would be better off attaching a dummy workbook.

The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

**sir yes sir** · 02-10-2011, 04:11 AM

Originally Posted by DonkeyOte

To best describe or illustrate your problem you would be better off attaching a dummy workbook.

The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

Since this is a spreadsheet that I plan on sharing with the public anyways, I'm just going to upload the original.

**sir yes sir** · 02-10-2011, 04:16 AM

Alright. The file should open with a pink column, which is column H.

Cell H396 is the problem child. I have even changed to formula's range, but it still counts incorrectly. The count formula is only counting 22 instances of data <.5 and the status of "Starter" (column I)

Next, I went down the column that is in the range (column H, rows 3-389) and would delete and then undo the data in that column. Thinking, the one that isn't being counted won't change what is in H396. What I discovered is that the data in H30 is not being counted for some reason.

Any reason as to why would be nice. I'm sure I can just add another row and move my data down, but I would like to find out why this has happened in case it occurs on a more serious scale/matter next time.

Thanks,
Jamie

**DonkeyOte** · 02-10-2011, 04:23 AM

Jamie, a quick way to check these type of oddities is to add wildcards to see if the results change, eg:

Please Login or Register  to view this content.

the result changes to 22... this tells us one of your values in I3:I389 (I30) contains trailing spaces

had the result stayed at 21 we would have moved the wildcard, "*Starter" to look/test for leading spaces