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

[HawaiianShirts]

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.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date
[/TD]
[TD]Description
[/TD]
[TD]Amount
[/TD]
[/TR]
[TR]
[TD]October 20, 2014
[/TD]
[TD]Maintenance Fee
[/TD]
[TD]$25.00
[/TD]
[/TR]
[TR]
[TD]November 1, 2014
[/TD]
[TD]Interest
[/TD]
[TD]$4.25
[/TD]
[/TR]
[TR]
[TD]November 6, 2014
[/TD]
[TD]Maintenance Fee
[/TD]
[TD]$50.00
[/TD]
[/TR]
[TR]
[TD]November 10, 2014
[/TD]
[TD]Consultation
[/TD]
[TD]$75.00
[/TD]
[/TR]
[TR]
[TD]November 11, 2014
[/TD]
[TD]Maintenance Fee
[/TD]
[TD]$25.00
[/TD]
[/TR]
</tbody>[/TABLE]


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?

 

[steve the fish]

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)

 

[Peter_SSs]

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)

 

[HawaiianShirts]

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?

 

[steve the fish]

Simply it will change a TRUE or FALSE result into 1 for TRUE and 0 for FALSE.

 

[Peter_SSs]

There is also some further information here.