HawaiianShirts
New Member
- Joined
- Jul 19, 2014
- Messages
- 15
- Office Version
- 365
- Platform
- Windows
I did some research and wrote out what I thought was a working formula, but it returned a zero. That's not what I was after.
To set the stage...
Worksheet 1 includes lots of data points. The relevant ones are dates (Column A), descriptions (Column D) and dollar amounts (Column F).
Worksheet 2 is doing some calculations for me. One cell (B7) has a MAX formula to get the most recently-entered date from Worksheet 1's Column A. That works fine. It's the one next to it (C7) that's giving me trouble.
What I want is to make Excel look at that MAX value in B7 and see the month and year (November 2014, in this case). Then I want it to look at Worksheet 1's date column and find all the dates that are in November 2014. Then I want it to look for specific text in the description column (for example, "Maintenance Fee") and give me a SUM of the values in the dollar amounts column that meet those criteria.
So if I have this data set, then I get a SUM of Rows 4 and 6.
<tbody>
</tbody>
Here's the formula I tried based on what knowledge I thought I had:
=SUMIFS(Worksheet1!F:F,Worksheet1!A:A,(MONTH,Worksheet1!A:A)=(MONTH,B7),Worksheet1!D:D,"Maintenance Fee")
One obvious problem I'm seeing now is that the MONTH parts will ignore the year part of the date, but I suspect that this formula is fundamentally incorrect anyway.
Any suggestions?
To set the stage...
Worksheet 1 includes lots of data points. The relevant ones are dates (Column A), descriptions (Column D) and dollar amounts (Column F).
Worksheet 2 is doing some calculations for me. One cell (B7) has a MAX formula to get the most recently-entered date from Worksheet 1's Column A. That works fine. It's the one next to it (C7) that's giving me trouble.
What I want is to make Excel look at that MAX value in B7 and see the month and year (November 2014, in this case). Then I want it to look at Worksheet 1's date column and find all the dates that are in November 2014. Then I want it to look for specific text in the description column (for example, "Maintenance Fee") and give me a SUM of the values in the dollar amounts column that meet those criteria.
So if I have this data set, then I get a SUM of Rows 4 and 6.
Date | Description | Amount |
October 20, 2014 | Maintenance Fee | $25.00 |
November 1, 2014 | Interest | $4.25 |
November 6, 2014 | Maintenance Fee | $50.00 |
November 10, 2014 | Consultation | $75.00 |
November 11, 2014 | Maintenance Fee | $25.00 |
<tbody>
</tbody>
Here's the formula I tried based on what knowledge I thought I had:
=SUMIFS(Worksheet1!F:F,Worksheet1!A:A,(MONTH,Worksheet1!A:A)=(MONTH,B7),Worksheet1!D:D,"Maintenance Fee")
One obvious problem I'm seeing now is that the MONTH parts will ignore the year part of the date, but I suspect that this formula is fundamentally incorrect anyway.
Any suggestions?