Problem with Countif.

I am at a loss as to why the countif formula as showning column "H" won't show the number 8 in the count list.

The goal is to produce a list of numbers to be used as the match portion of an Index,Match formula. As shown I get a gap in the display where the number 8 should be.

Any ideas as to what I am missing?

Thanks

Jim O

Hi.

You mean your formula in H13 should show that value?

But the formula in that cell is:

=SUM(IF(G13<>1,COUNTIF(G$2:G13,G13),0),H12)

which evaluates as:

=SUM(IF(TRUE,COUNTIF(G$2:G13,G13),0),H12)

i.e.:

=SUM(IF(TRUE,2,0),H12)

i.e.:

=SUM(2,H12)

i.e.:

=SUM(2,7)

which is 9, so I'm not sure why you were expecting this to result in 8.

Did you perhaps not notice that the COUNTIF part would here return 2, since there are that number of occurrences of 31249 in the range G2:G13?

Regards

Hi Jim - it is happening because date in cell G13 = 31249 has repeated twice in the column G from G2 to G13 and when the formula in column H13 is calculating it found that (see below)

H13 "=SUM(IF(G13<>1,COUNTIF(G$2:G13,G13),0),H12)"

Then It goes into calculation

=SUM(IF(TRUE,2,0), 7) -- > =SUM(2,7) = 9

Please let me know if you have got the cause of your problem and might be now you have the solutions is with you. (I think so)

=COUNTIF($G$2:G2;">1")
Try this formula in cell H2 en pull down the formula

And If you simple want where column G has 1, H also should return to 0 else it will keep increasing +1, then you can make the few changes in your formula and get the same.

Below is the updated formula you can keep it in H2 and drag down.

Formula:

=SUM(IF(G2<>1,IF(COUNTIF(G2:G$2,G2)>=1,1),0),H1)

Jim - I am talking about the Row number 7 (G7) and Row no 13 (G13) where both the dates are same as July 21 due to that when the formula is coming and counting in in H13, it returns to count 2 for the G13 date as it has already appeared in G7. Hope this time my understanding on your problem is correct.