Array formula

**mkhagerty** · 01-02-2012, 11:57 AM

Happy New Year everyone.

I'm working on a spreadsheet designed to track my personal finances on a weekly basis. See the attached file. I want the formula to look at the master list of transactions on the left side of the spreadsheet and display the total debit amount for a particular category if it is within a specific date range. I'm using the array formula {=SUM(($A$2:$A$16<=I$5)*($B$2:$B$16="Mortgage")*($D$2:$D$16))} but I can't get it to look at only the date range that corresponds to the specific week.

I'm open to using other formulas if there is something else that will work but in my research the array formula seemed to be the best suited. Any help would be much appreciated and if you need clarification feel free to message me back.

Thanks,

**Andrew-R** · 01-02-2012, 12:22 PM

Just a cell reference typo, I think.

I changed the formula in G3 to:

=SUM(($A$2:$A$16<=G$2)*($A$2:$A$16>=F$2)*($B$2:$B$16=F3)*($D$2:$D$16))

And copied that to the other cells, and it seemed to work fine.

**dilipandey** · 01-02-2012, 12:22 PM

Hi mkhagerty,

Welcome to the forum and have a lovely year ahead.

Have a look at the attached file where I have tried solving your problem. Let me know if this works for you.

Cheers

Regards,
DILIPandey

<click on below 'star' if this helps>

**Ron Coderre** · 01-02-2012, 12:25 PM

Using your posted workbook...
These regular formulas return the total activity for the referenced Description that is between the referenced start and end dates:

For Excel 2007+

G3: =SUMIFS($D:$D,$B:$B,F3,$A:$A,">="&F$2,$A:$A,"<="&G$2)

For Excel 2003

G3: =SUMPRODUCT(($B$2:$B$1000=F3)*($A$2:$A$1000>=F$2)*($A$2:$A$1000<=G$2)*$D$2:$D$1000)

Copy either formula down as far as you need and into the other summary columns.

Is that something you can work with?

**NBVC** · 01-02-2012, 12:25 PM

How about?

=SUMPRODUCT(--($A$2:$A$16>=F$2),--(A2:A16<=G$2),--($B$2:$B$16=F3),$D$2:$D$16)

copied down.

Then you can copy G3:G14 and paste to other tables.

**Andrew-R** · 01-02-2012, 12:27 PM

Heh, I just came back to this thread to post the SUMPRODUCT alternative, but the big boys have been me to it

**reachhari** · 01-02-2012, 12:33 PM

Hi,

By looking at your profile, I could understand that you are using excel 2007 version, since you are using 2007 version. Your solution can be arrived very easily by using SUMIFS solution.

Check out the attachment and let me know your thoughts.