Summing of Alternating Columns

**heyhsia** · 05-14-2019, 03:15 PM

Hello,

I have a table with rows of donors and columns with alternating gift dates and gift amounts. I'm trying now to sum Gifts within 12 months of the first gift date ("Giving Year 1"), 13 to 24 months of the first gift date ("Giving Year 2").

I'm having trouble with using a sumproduct formula, stuck on the 2nd argument which I know is wrong. This is a variation of a prior issue that forum member ChemistB helped me with. I've tried a number of different date solutions but am stymied. I'd be grateful for any guidance. Thank you.

Regards,

Paul

**TMS** · 05-14-2019, 03:29 PM

For the Sum:

Formula:

Please Login or Register  to view this content.

And for the Count:

Formula:

Please Login or Register  to view this content.

**heyhsia** · 05-14-2019, 03:34 PM

Hi TMS, thanks for your reply.

I need to sum not all the dates, but for dates within 12 months and 24 months of the first gift date.

**Melvosh** · 05-14-2019, 04:12 PM

Based on TMS's formula, for Giving Year 1:

Please Login or Register  to view this content.

For Giving Year 2:

Please Login or Register  to view this content.

Change the red numbers for subsequent years. Does that give you what you're looking for?

**Jacc** · 05-14-2019, 04:25 PM

Edit:
How about this in E4:

Formula:

Please Login or Register  to view this content.

Copy in all directions.

**TMS** · 05-14-2019, 04:55 PM

@Melvosh: Thank you. My work here is done

**Melvosh** · 05-14-2019, 05:02 PM

@TMS, you did all the hard work, I just adapted

@heyhsia, both the solution I offered and the solution Jacc offered look at column B for the initial date. You'll want to make sure that's equal to or before the date in column L, otherwise there will be gift amounts that are not included.

**heyhsia** · 05-14-2019, 05:18 PM

TMS, Melvosh and Jacc, thank you for your replies.

I got Melvosh's formula to work for various time periods (12, 24, 36 months). I was unable to get Jacc's formula to work, which I'm sure is due to lack of understanding of how the formula works; the use of sumproduct is new to me, as are mod/column functions. I will continue to study these formulas and try to modify them.

One thing I noticed is that Melvosh's formula does not allow me to add new columns to the file. Is there a way to modify the formula that would permit me to add columns?

Thanks again,

Paul

**Jacc** · 05-14-2019, 05:25 PM

Seems to work. I first wrote cell E3 but of course I meant E4.

**TMS** · 05-14-2019, 05:27 PM

You're welcome.

One thing I noticed is that Melvosh's formula does not allow me to add new columns to the file. Is there a way to modify the formula that would permit me to add columns?

Add columns where? My formula and Melvosh's adaptation cater for the columns in your sample file. If you were to add columns at the right hand end, you would just need to change the last column reference.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

**heyhsia** · 05-14-2019, 05:37 PM

Jacc, thanks for the file.

In Melvosh's and Jacc's recent file, adding a column between say Columns E and F set all the results to zero.

**TMS** · 05-14-2019, 05:49 PM

The formulae are trying to determine "every other column" based on their current position. If you insert a single column, what were odd columns will become even and vice versa. You'd need to adjust the column reference in some way. In my original SUM formula, that would look like:

Formula:

Please Login or Register  to view this content.

**heyhsia** · 05-14-2019, 05:59 PM

Got it. Thanks TMS. I'll work it out.

Thanks again Melvosh and Jacc.

**TMS** · 05-14-2019, 06:02 PM

You're welcome. Thanks for the rep.

**Jacc** · 05-14-2019, 06:44 PM

'''''''

Summing of Alternating Columns

LinkBack

Thread Tools

Rate This Thread

Display

Summing of Alternating Columns

Re: Summing of Alternating Columns

Re: Summing of Alternating Columns

Re: Summing of Alternating Columns

Re: Summing of Alternating Columns

Re: Summing of Alternating Columns

Re: Summing of Alternating Columns

Re: Summing of Alternating Columns

Re: Summing of Alternating Columns

Re: Summing of Alternating Columns

Re: Summing of Alternating Columns

Re: Summing of Alternating Columns

Re: Summing of Alternating Columns

Re: Summing of Alternating Columns

Re: Summing of Alternating Columns

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Conditional Summing of Alternating Columns

Merging 2 columns into 1 alternating every other column

delete columns alternating

Alternating Values in Columns

[SOLVED] Collate information by alternating between two columns.

[SOLVED] VBA Looping Issue_Merging Alternating Columns

insert alternating columns and rows

Bookmarks

Bookmarks

Posting Permissions