Add numbers in x number of columns given specific criteria

**Met Uysh!** · 05-19-2009, 02:28 AM

Hi there

I have a workbook in Excel 2003 with two spreadsheet, a data sheet and a summary sheet. The data sheet contains account numbers in column A and monthly values from column B to M (April to March) for each account. Account numbers occur more than once in column A.

The summary sheet contains each account number as in the data sheet, but only once with a month column and a YTD (year to date) column. Part of my function is to report on a given month number as well as a year to date number as at that given month. For example if it is May, I need to report the results for May as well as April and May totals. Cell C 1 contains the value of the particular month (say month 1 equals April and month 12 equals March).

I need a formula or function to return the year to date results given a certain month (cell C1) for each account number. The sumif function does not help since it only returns values for 1 column. I need the formula to perform a sumif function but if I put in 2 in cell c1 I need the formula to add the values in columns B and C, if I put a 3 in cell C1 I need the formula to add the values in columns B, C and D.

**JONvdHeyden** · 05-19-2009, 02:34 AM

Try this in C2 and copy to end of range:

=SUMPRODUCT((datasheet!$A$2:$A$31=$A2)*(datasheet!$B$1:$M$1<=MOD(MONTH(TODAY())+8,12)+1),datasheet!$B$2:$M$31)

**DonkeyOte** · 05-19-2009, 02:35 AM

Or alternatively:

C2:
=SUMPRODUCT((datasheet!$A$2:$A$31=$A2)*(datasheet!$B$2:INDEX(datasheet!$B$2:$M$31,0,$B$1)))

Also you don't need to use an Array in B given you can use INDEX to create your one column range in a standard SUMIF:

B2:
=SUMIF(datasheet!$A$2:$A$31,$A2,INDEX(datasheet!$B$2:$M$31,0,$B$1))

**JONvdHeyden** · 05-19-2009, 02:38 AM

Yes, I believe Donkeys' INDEX method is more efficient. You can take best from both...

=SUMPRODUCT((datasheet!$A$2:$A$31=$A2)*(datasheet!$B$2:INDEX(datasheet!$B$2:$M$31,0,MOD(MONTH(TODAY())+8,12)+1)))

I'm assuming you need to work out the current month, hence the MOD thingy...

**DonkeyOte** · 05-19-2009, 02:39 AM

Jon, I think the month is specified in B1.

**JONvdHeyden** · 05-19-2009, 02:42 AM

Erm, yes, ok.

I assumed he wanted it worked out...

**Met Uysh!** · 05-19-2009, 02:53 AM

Wow, that was quick and surely efficient!!! Thanks guys!! Although I looked at the index and sumproduct formulas before I could not understand how to use it in conjunction. I've found the formula =SUMPRODUCT((datasheet!$A$2:$A$31=$A2)*(datasheet!$B$2:INDEX(datasheet!$B$2:$M$31,0,$B$1))) to be the easiest.

Thansk alot!!!

**DonkeyOte** · 05-19-2009, 02:55 AM

We're glad to have helped.

I would advise you also alter formulae in B as suggested so as to remove the Arrays which are not necessary in this particular instance.

Please remember to mark thread as solved (see FAQ if unsure)

**Met Uysh!** · 05-19-2009, 03:03 AM

Thanks, I've used the Sumif as suggested for the month with the Index formula. I am really impressed, I've searched for a week and found nothing. I posted this and in no time got the solution!