Sum cells 1:500, and count only every 3rd cell

[dickharlo]

Is there a way to select a range to sum, but have it only count every
3rd row.....where my range is something like 1 to 500?

Thanks

 

[Don Guillett]

=SUMPRODUCT(--(MOD(ROW(E1:E500),3)=1),E1:E500)

 

[Markus Fritsche]

Am Tue, 16 Sep 2008 12:38:52 -0700 (PDT) schrieb [email protected]:

Is there a way to select a range to sum, but have it only count every
3rd row.....where my range is something like 1 to 500?

I would suggest using sumif and an extra numbering column (1, 2, 3, 1, 2,
3, ...)

Kind regards,
Markus

 

[dickharlo]

=SUMPRODUCT(--(MOD(ROW(E1:E500),3)=1),E1:E500)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software






- Show quoted text -

That is a heck of a formula.....tricky. Can you tell me what is
driving this...the ',3' or the '=1'. I'm playing and getting desired
results, but for the life of me can't figure out why. What is it that
each is doing? [email protected].

Thanks!!!

 

[Don Guillett]

Look in the help index for MOD
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]

=SUMPRODUCT(--(MOD(ROW(E1:E500),3)=1),E1:E500)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software






- Show quoted text -

That is a heck of a formula.....tricky. Can you tell me what is
driving this...the ',3' or the '=1'. I'm playing and getting desired
results, but for the life of me can't figure out why. What is it that
each is doing? [email protected].

Thanks!!!

 

[christopherphartley]

Look in the help index for MOD
--
Don Guillett
Microsoft MVP Excel
SalesAid Software





That is a heck of a formula.....tricky.  Can you tell me what is
driving this...the ',3' or the '=1'.   I'm playing and getting desired
results, but for the life of me can't figure out why.  What is it that
each is doing?  [email protected].

Thanks!!!

I'm looking to do something similar, but I need an IF in there and I'm
summing columns not rows.
For example: I want to SUM A1,A3,A5... but not until the adjacent cell
(A2,A4,A6...) equals some qualifier.

Any help?

 

[Dave Peterson]

Maybe something like:

=SUMIF(A2:A88,"some qualifier",A1:A87)

 

[christopherphartley]

Maybe something like:

=SUMIF(A2:A88,"some qualifier",A1:A87)

[email protected] wrote:

Ooops, sorry.... I typed that wrong. I meant to say:
I want to SUM A1,C1,E1.... but not until the adjacent cell
(B1,D1,F1...) equals some qualifier.

That makes it a little more difficult, but hopefully not impossible.

 

[Dave Peterson]

Maybe...

=sumif(a1:y1,"some qualifier",b1:z1)

Ooops, sorry.... I typed that wrong. I meant to say:
I want to SUM A1,C1,E1.... but not until the adjacent cell
(B1,D1,F1...) equals some qualifier.

That makes it a little more difficult, but hopefully not impossible.

 

[Paul Hyett]

Am Tue, 16 Sep 2008 12:38:52 -0700 (PDT) schrieb [email protected]:


I would suggest using sumif and an extra numbering column (1, 2, 3, 1, 2,
3, ...)

I wish there was an 'Averageif' function, though.

 

[RagDyeR]

Dave,
You're Backwards to the OP's request:

=sumif(B1:Z1,"some qualifier",A1:Y1)

--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

Maybe...

=sumif(a1:y1,"some qualifier",b1:z1)

 

[RagDyeR]

*BUT* ... his newest request above matches your suggestion.

You're into mind-reading now also ? ? ?<bg>
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

Dave,
You're Backwards to the OP's request:

=sumif(B1:Z1,"some qualifier",A1:Y1)

--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

Maybe...

=sumif(a1:y1,"some qualifier",b1:z1)

 

[Peo Sjoblom]

Excel 2007 has a new function called AVERAGEIF
--


Regards,


Peo Sjoblom

 

[Dave Peterson]

Psychic = Psycho????

*BUT* ... his newest request above matches your suggestion.

You're into mind-reading now also ? ? ?<bg>
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

Dave,
You're Backwards to the OP's request:

=sumif(B1:Z1,"some qualifier",A1:Y1)

--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

Maybe...

=sumif(a1:y1,"some qualifier",b1:z1)

 

[Paul Hyett]

Excel 2007 has a new function called AVERAGEIF

Thanks for the reply, but I'd rather do without it than switch to the
dreaded Excel 2007!