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

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

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

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

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

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