nth row or column counting

M

monza_racer

I have a spreadsheet with two rows of data for each person (Row A and
Row B). I would like to COUNT or COUNTA every other cell that contains
data (or every nth cell) in a column, in this case I'd like to count
only the Row A cells that contain data. I know this can be done with
the SUM function, but I only want a count as the result, not a sum. I
tried altering the SUM formula structure, but it doesn't carry over to
COUNT or COUNTA functioning. Is there a formula to handle this, or
must a macro be developed? Thanks.
 
B

Bernie Deitrick

To count filled cells in column B that are on the odd rows:

=SUMPRODUCT((MOD(ROW(B2:B1000),2)=1)*(B2:B1000<>""))

for the even rows

=SUMPRODUCT((MOD(ROW(B2:B1000),2)=0)*(B2:B1000<>""))

HTH,
Bernie
MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top