0

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)
Improve this question
1
  • @tigeravatar, Your comment seems like a legitimate attempt to answer the question. If so, I strongly recommend you post it as an answer. Commented Jan 19, 2016 at 19:00

1 Answer 1

Reset to default
1

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

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

I'm trying to create a dynamic calculation of each income or expense during a two week period. Using the first three columns might only give the first instance... Each of those types may have variable amounts within a year (could have three paydays in a month, for instance), so having an average amount isn't useful.
@Steve I'm confused by your comment. This only pulls a sum between the specified dates for the specified type of income/expense. It also doesn't pull the first instance, nor does it average the numbers.
I used this formula using those columns (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 amount 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 that would search for: 1) within a given date rage, and 2) for each Type, then return the Sum of the amounts found... Sorry if my original explanation wasn't detailed enough.
@Steve What happens when you try the formula I posted? Using your sample data, it should return the results you're looking for. If it doesn't, can you please edit your original question to provide sample data where it doesn't work and show the incorrect results along with what you expect the correct results to be?
This problem with that formula is that it can only return the first instance of the income/expense amount because only one instance is in the data. I'm looking to return each month's Total amount for each type, which would be dynamic if, say, a person is paid every two weeks (once a month wouldn't cause that issue).
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.