Excel Question - getting COUNTIF to count odd or even number rows only?

I know to get the sum of an odd or even row you do something like this:

=SUM(IF(MOD(ROW(A1:A3),2)=1,A1:A3,0))

Taken from http://support.microsoft.com/kb/191008

But what would be the ideal formula to use if I needed countif to count a number of odd or even rows?

Current I'm using this formula:

=COUNTIF(Sheet3!$A$5:$A$16,D7)

In the above COUNTIF example, I would like to get it to just count the A5, A7, A9, etc... (based upon a criteria on D7), but I'll need it to count the even numbers later on for a different output. Currently, as you can see, it only counts everything in the range.

I've messed around with applying COUNTIF to the above SUM formula example from the help site, but keep getting errors. Please help if you know, thanks!

Excel Question - getting COUNTIF to count odd or even number rows only?microsoft exchange

To count the odd numbered rows in a range:

=SUMPRODUCT(--(MOD(ROW(A$5:A$16)

-ROW(A$5),2)=0))

To count the even numbered rows in a range:

=SUMPRODUCT(--(MOD(ROW(A$5:A$16)

-ROW(A$5),2)%26gt;0))

Note that these formulas are robust against new row insertions before the range. For example, if you insert a new row 1 the formulas will automatically account for that.

Biff

Microsoft Excel MVP

Excel Question - getting COUNTIF to count odd or even number rows only?windows vista ultimate internet explorer

I think I see what you want but not absolutely sure. I'll give you my fix and you can manipulate any way you want.

I will call a range from A1 to A8 - range "aa". In a blank column C I will type "odd" (not the quotes) in row 1 and "even" (not the quotes) in row 2. Highlight column C from row 1 to row 9 (Whatever you call "aa"). Now click edit/fill/series/autofill/OK and you should have alternating odd/even down col C.

Next in a blank column E put the following in E1:

IF(C1="odd",

COUNTIF(A1,$D$7),"")

And the following in F1:

IF(C1="even",

COUNTIF(A1,$D$7),"")

Now highlight E1 %26amp; F1 to the end of your range (in my ex. down to row 9). Click edit/fill/down.

Now use the SUM formula (in my ex.) at E9 and F9.

E9 gives "odd" row totals and F9 gives "even" row totals.

I maintained the use of D7 as the cell with your criteria.

NOTE: I split the two IF formulas in half so they would show completely on Yahoo. Otherwise the view is truncated. Yo need to type both lines as one formula in both cases.