counting every 3 rd row

's Computer Specifications

The formula "=countif(a1:a1000,"A??")" is working, but i have to count every
3rd row from a1 to a1000 like a1,a4,a7 etc., How to write formula to do this
task.
Thanks for reply

's Computer Specifications

=SUMPRODUCT(--(MOD(ROW(A1:A1000),3)=1),A1:A1000)

"ezil" wrote:

> The formula "=countif(a1:a1000,"A??")" is working, but i have to count every
> 3rd row from a1 to a1000 like a1,a4,a7 etc., How to write formula to do this
> task.
> Thanks for reply

's Computer Specifications

"joel" wrote:

> =SUMPRODUCT(--(MOD(ROW(A1:A1000),3)=1),A1:A1000)
>
> "ezil" wrote:
>
> > The formula "=countif(a1:a1000,"A??")" is working, but i have to count every
> > 3rd row from a1 to a1000 like a1,a4,a7 etc., How to write formula to do this
> > task.
> > Thanks for reply

Thanks for the formula but where to put the condition How the formula like
with the condition

's Computer Specifications

i had to do it like this
in column B put
=MOD(Row(),3)

this gives a 0 for rows, 3,6,9 ....
then in another cell

=SUM((B1:B100=0)*(A1:A100="A"))

entered as an array formula

this, B1:B100=0 , is what gives values for every third row and this
A1:A100="A", counts A's

the array formula thus counts where col B=0 and Col A is "A"

"ezil" <(E-Mail Removed)> wrote in message
news:199BBF00-A5C6-42B6-978C-(E-Mail Removed)...
>
>
> "joel" wrote:
>
>> =SUMPRODUCT(--(MOD(ROW(A1:A1000),3)=1),A1:A1000)
>>
>> "ezil" wrote:
>>
>> > The formula "=countif(a1:a1000,"A??")" is working, but i have to count
>> > every
>> > 3rd row from a1 to a1000 like a1,a4,a7 etc., How to write formula to do
>> > this
>> > task.
>> > Thanks for reply
>
> Thanks for the formula but where to put the condition How the formula like
> with the condition

's Computer Specifications

=SUMPRODUCT(--(MOD(ROW(A1:A1000),3)=1),--(A1:A1000="A??"))

"ezil" wrote:

>
>
> "joel" wrote:
>
> > =SUMPRODUCT(--(MOD(ROW(A1:A1000),3)=1),A1:A1000)
> >
> > "ezil" wrote:
> >
> > > The formula "=countif(a1:a1000,"A??")" is working, but i have to count every
> > > 3rd row from a1 to a1000 like a1,a4,a7 etc., How to write formula to do this
> > > task.
> > > Thanks for reply
>
> Thanks for the formula but where to put the condition How the formula like
> with the condition

Thread Tools
Show Printable Version Email this Page
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
counting function but not double counting duplicates	=?Utf-8?B?SlJE?=	Microsoft Excel Worksheet Functions	2	7th Nov 2007 06:43 PM
Counting rows, then counting values.	Michael via OfficeKB.com	Microsoft Excel Misc	7	4th Aug 2005 10:57 PM
Counting Rows Then Counting Values in Columns	Michael via OfficeKB.com	Microsoft Excel Programming	1	1st Jun 2005 04:10 PM
Counting names in a column but counting duplicate names once	=?Utf-8?B?VEJvZQ==?=	Microsoft Excel Misc	9	11th May 2005 11:24 PM
12,000 and counting - dead, 1 Million and counting - homeless	David Candy	Windows XP General	34	1st Jan 2005 10:27 PM