how do I count every third cell?

**lordFRZA** · 08-20-2009, 04:50 PM

I can't figure out which one or how to use count, countif, or countifs correctly.
here is an example of what i need done:

I need to how many cells that have a 0 in them
The cells I need counted are D5,G5,J5,M5...etc. Every 3rd one
But NOT the ones in between.

I can get it to count all 0's in all the cells in the row but that's not what I need.
If some one could show me a formula from the example I have given, I'm sure I could apply it to my spread sheets with ease.

Thank You

**shg** · 08-20-2009, 04:57 PM

Welcome to teh forum.

Perhaps =SUMPRODUCT( (MOD(COLUMN(D5:Z5)-COLUMN(D5), 3) = 0) * (D5:Z5=0) )

Blank cells will be counted as zero.

**NBVC** · 08-20-2009, 05:00 PM

TRY

=SUM(IF(ISNUMBER(D5:X5),IF(D5:K5=0,IF((MOD(COLUMN(D5:X5)-COLUMN(D5),3)=0),1))))

Where D5:X5 is the entire range to count in..

confirmed with CTRL+SHIFT+ENTER not just ENTER

**lordFRZA** · 08-20-2009, 11:11 PM

thanks for the help guys.
smg, is there a way to make the blank cells not count as zeros?

Also what if the cells are not evenly spaced. Is there a way to just type in the cells you want to count zeros in?

**NBVC** · 08-21-2009, 08:37 AM

My formula does not count blanks...try it.

To count non-contiguous cells with a condition, try:

=SUM(COUNTIF(INDIRECT({"E6","G6","I6","L6","O6"}),0))

replace cell refs between quotes with your cell refs or sub-ranges...

This will not count blanks..just zeroes

**martindwilson** · 08-21-2009, 08:58 AM

modifying shg's original try for non blanks
=SUMPRODUCT( (MOD(COLUMN(D5:Z5)-COLUMN(D5), 3) = 0) * (D5:Z5=0)*(D5:Z5<>"") )

**lordFRZA** · 08-23-2009, 12:57 AM

thanks alot guys
I am using NBVC's non-contiguous cells formula. I forgot I need to skip the third cell every third time and then count those seperately. So it's just easier to input each cell I need in each formula.
But now I have run into another problem. I have gotten the formula worked out on one row but now I need to paste the formula into the rest of the rows. But it keeps coming up exactly as it does in the first row instead of changing the row numbers in the formula to correspond with the row that formula is in. What do I need to do

**daddylonglegs** · 08-23-2009, 07:34 AM

Try like this

=SUM(COUNTIF(INDIRECT({"E","G","I","L","O"}&ROW()),0))

**lordFRZA** · 08-23-2009, 04:29 PM

thanks everyone

Thread: how do I count every third cell?

Thread Tools

Display

how do I count every third cell?

Re: how do I count every third cell?

Re: how do I count every third cell?

Re: how do I count every third cell?

Re: how do I count every third cell?

Re: how do I count every third cell?

Re: how do I count every third cell?

Re: how do I count every third cell?

Re: how do I count every third cell?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions