How to Find a Value if a Date Includes a Referenced Month

HawaiianShirts

New Member
Joined
Jul 19, 2014
Messages
15
Office Version
  1. 365
Platform
  1. 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.
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?
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Firstly make sure all your dates are true dates not text. Same for your dollar amounts then try this:

=SUMPRODUCT(--(MONTH(A2:A1000)=MONTH(B7)),--(YEAR(A2:A1000)=YEAR(B7)),--(D2:D1000="Maintenance Fee"),F2:F1000)
 
Upvote 0
Solution
Firstly, I would try to avoid whole column references if possible. Why calculate over 1 million rows if you are not using them? Pick some smaller number but still big enough to be sue to cover your likely maximum data size. I'll use 1,000 like Steve did.

Just offering another way to deal with the month/year issue.

=SUMPRODUCT(--(TEXT(Worksheet1!A2:A1000,"mmyy")=TEXT(B7,"mmyy")),--(Worksheet1!D2:D1000="Maintenance Fee"),Worksheet1!F2:F1000)
 
Upvote 0
It worked! Calculations are coming in just the way I want them now. I did trim back the reference range like you both suggested to about 10,000.

I have never tried SUMPRODUCT before because I didn't know how it worked. What are the pairs of hyphens for?
 
Upvote 0
Simply it will change a TRUE or FALSE result into 1 for TRUE and 0 for FALSE.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top