Using dates with SUMIFS(INDEX(Match,,,)

**AnyBrain** · 12-09-2014, 11:45 AM

Hi there

I have done a simple TblBankData with headers [Date][Category][Amount]. Using =SUMIFS(INDEX(TblBankData,,MATCH(F$1,TblBankData[#Headers],0)),TblBankData[[Category]:[Category]],$E2) I can add the total amount spent in the table for a given category.

I would like to show <Category><Amount> grouped under <Month> columns. Now I know I can easily do this with a PIVOT chart; but I would like to be able to do this with an equation ..

Any ideas?

Many Thanks,
Ian

**Bernie Deitrick** · 12-09-2014, 01:49 PM

Ian,

I not sure why you are using INDEX(TblBankData,,MATCH(F$1,TblBankData[#Headers],0)) if you truly have a table with just Date Category and Amount when TblBankData[[Amount]:[Amount]] will work.

With Jan 1 2014 in F1, Feb 1 in G1, Mar 1 in H1, etc, and the categories of interest in column E starting in E2, then use this in F2, and copy down and across, and it will report month sums for each category:

=SUMIFS(TblBankData[[Amount]:[Amount]],TblBankData[[Category]:[Category]],$E2,TblBankData[[Date]:[Date]],">=" & F$1,TblBankData[[Date]:[Date]],"<" & G$1 )

**AnyBrain** · 12-09-2014, 02:59 PM

Thank you; I'm just starting to move on from Pivot tables and Lookup's nd got locked into looking for an over-complicated solution. As ever the simple things are best, many thanks, Ian