CODEWITHSUNDEEP
excelexcel-formula
Steve
Steve

Reputation: 1

Multiple criteria to sum a range of data

I'm using Excel 2013:

I calculated when income/expense would fall on a given date in the year: Budget sheet on top, Event Calc sheet on bottom

On my "Budget Adjust" sheet, I then wanted to sum the amounts with the criteria of: 1) within two dates and 2) matching the income/expense type:

While I now understand that I can't use SUMIFS (because my data to sum and date criteria are different sizes), I'm wondering if any of you have a brilliant idea about how to sum the data for specific date ranges and a type match.

ADDITIONAL INFO: Using the data in the first few columns, I created a dynamic formula for each day in the year, so columns T:NV, with a day of the year in Row 1: =IF($D2<>0,IF(AND(U$1>=$C2,(U$1-$C2)/$D2=ROUND(((U$1-$C2)/$D2),0)),$B2,0),IF(DAY(U$1)=$E2,$B2,0)).

For that day of the year, for the type of income/expense, it would enter either the income/expense that would fall on that day, or zero. There are 365 such columns (T:NV), one for each year.

My attempt was to use the range of data (T2:NV20) in one formula on the Budget Adjust sheet that would search for: 1) within a given date rage, and 2) for each Type, then return the Sum of the amounts found within that date range

--- EDIT ---
I want an elegant answer, with one formula in the results field, but here's how I've made it work:

I'm limited to two pics per post, so here is a pic of both sheets: My work-around to get the answer

On top is is the Event Calc page, where I've created a column for each month, and H2 is highlighted with the formula above:

=SUMIFS($U2:$NV2,$U$1:$NV$1,">="&H$1,$U$1:$NV$1,"<"&I$1)

On the bottom is the Budget sheet with D9 (Salary for Jan-2016) highlighted: using this formula:

=SUMIFS('Event Calc'!H$2:H$20,'Event Calc'!$A$2:$A$20,"="&$B9)

Upvotes: 0

Views: 167

Answers (1)

tigeravatar
tigeravatar

Reputation: 26670

Why wouldn't you just use 'Event Calc' columns A, B and C? That is already in a proper format for a SUMIFS formula. On sheet "Budget Adjust" cell D17, use this formula:

=SUMIFS('Event Calc'!$B:$B,'Event Calc'!$A:$A,$B17,'Event Calc'!$C:$C,">="&D$10,'Event Calc'!$C:$C,"<="&D$11)

and then copy right and down

Upvotes: 1

Related Questions