Sum Count of Criteria Every 3rd Row
 

Hi All,

I would like to Sum the Count of numerical values in Every 3rd Row BUT ONLY
IF the value(s) equals a specific criteria.

Example:
Sum Count of Every 3rd Row that has a value of 50. The criteria will vary.

Cheers,
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200512/1

Sum Count of Criteria Every 3rd Row
 

if I read your question right you want to sum the occurances of 50, but only
those in every 3rd row.

So, for the range of a1:a10, summing those 50's in rows 1,4,7,10

=SUMPRODUCT((A1:A10=50)*(MOD(ROW(A1:A10)-1,3)=0)*(A1:A10))

you could refer to cells with the values of 50 and 3 in the formula if
desired vs the hard coding of the values in the formula

"Sam via OfficeKB.com" wrote:

Hi All,

I would like to Sum the Count of numerical values in Every 3rd Row BUT ONLY
IF the value(s) equals a specific criteria.

Example:
Sum Count of Every 3rd Row that has a value of 50. The criteria will vary.

Cheers,
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200512/1

Sum Count of Criteria Every 3rd Row
 

Assuming that A1:A100 contains your data, to count every third row,
starting with the first cell in the range, try...

=SUMPRODUCT(--(MOD(ROW(A1:A100)-ROW(A1)+0,3)=0),--(A1:A100=B1))

....where B1 contains your criteria. To count every third row, starting
with the third cell in the range or the first occurrence of third,
change the +0 bit to +1.

Hope this helps!

In article <5898fe5a1a483@uwe, "Sam via OfficeKB.com" <u4102@uwe
wrote:

Hi All,

I would like to Sum the Count of numerical values in Every 3rd Row BUT ONLY
IF the value(s) equals a specific criteria.

Example:
Sum Count of Every 3rd Row that has a value of 50. The criteria will vary.

Cheers,
Sam

Sum Count of Criteria Every 3rd Row
 

Hi Duane,

Thank you for reply. I've tried the solution below, but do not get the
expected answer. Not sure why?

=SUMPRODUCT((A1:A10=50)*(MOD(ROW(A1:A10)-1,3)=0)*(A1:A10))

Cheers,
Sam

duane wrote:
if I read your question right you want to sum the occurances of 50, but only
those in every 3rd row.

So, for the range of a1:a10, summing those 50's in rows 1,4,7,10

=SUMPRODUCT((A1:A10=50)*(MOD(ROW(A1:A10)-1,3)=0)*(A1:A10))

you could refer to cells with the values of 50 and 3 in the formula if
desired vs the hard coding of the values in the formula

Hi All,

[quoted text clipped - 6 lines]
Cheers,
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200512/1

Sum Count of Criteria Every 3rd Row
 

Hi Domenic,

Thank you for reply. The Formula works Great!

=SUMPRODUCT(--(MOD(ROW(A1:A100)-ROW(A1)+0,3)=0),--(A1:A100=B1))

Cheers,
Sam


Domenic wrote:
Assuming that A1:A100 contains your data, to count every third row,
starting with the first cell in the range, try...

=SUMPRODUCT(--(MOD(ROW(A1:A100)-ROW(A1)+0,3)=0),--(A1:A100=B1))

...where B1 contains your criteria. To count every third row, starting
with the third cell in the range or the first occurrence of third,
change the +0 bit to +1.

Hope this helps!

Hi All,

[quoted text clipped - 6 lines]
Cheers,
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200512/1

Sum Count of Criteria Every 3rd Row
 

Hi,

Try the following array formula (Ctrl+Shift+Enter). Assume your data is in
range A13:A17

23
34
56
45
34

In A19, enter the following array formula (Ctrl+Shift+Enter)

=SUM(IF((MOD(ROW(A13:A17),2)=0*(A13:A174)),$A$13: $A$17,0))

The result of this will be 79.

If you have any problems please feel freet o contact me at


Regards,


"Sam via OfficeKB.com" wrote:

Hi All,

I would like to Sum the Count of numerical values in Every 3rd Row BUT ONLY
IF the value(s) equals a specific criteria.

Example:
Sum Count of Every 3rd Row that has a value of 50. The criteria will vary.

Cheers,
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200512/1