SUMPRODUCT with month and criteria

[Kirbstomp1287]

I am trying to use a SUMPRODUCT function with the MONTH function to get a monthly count of specific data.

Essentially I need the function to look for the month (formatted: 1/25/19), then the initials from box AB4 in column D, then the text "Forecast" from column J to get a count of text "Met DLT" from column AP.

Here is the code I have so far that gives me a zero but should be 36.

=SUMPRODUCT(--(MONTH(F2:F9999)=6)*(J2:J9999="Forecast")*(AP2:AP9999="Met DLT")*(D2:D9999=AB4))

Any help would be much appreciated!

 

[jasonb75]

Your formula looks fine, either your dates are not valid (text dates imported from another source), or one of the other columns is not an exact match for the criteria specified.

You can test if the dates are valid by using

=COUNT(F2:F9999)

The COUNT function will count proper dates, but not text dates.

 

[Kirbstomp1287]

Your formula looks fine, either your dates are not valid (text dates imported from another source), or one of the other columns is not an exact match for the criteria specified.

You can test if the dates are valid by using

=COUNT(F2:F9999)

The COUNT function will count proper dates, but not text dates.

So the count function works, and I have a sheet for yearly totals that uses the COUNTIF function without using the month function, and it works just fine. Should my last array be *(COUNT(F2:F9999=AB4))?

 

[Kirbstomp1287]

Whoops I mean (COUNT(AP2:AP9999="Met DLT"))

 

[joeu2004]

Upload an example Excel file that demonstrates the problem. The forum now supports that, apparently. The devil is in the details.

I suspect that some of the ranges have data with extra spaces or non-breaking spaces. Thus, the data is not what it appears to be.

And COUNT(AP2:AP9999="Met DLT") [sic] is meaningless. If anything, you might write =COUNT(AP2:AP9999, "Met DLT") and similarly for each of the other conditions.

But first, these are just diagnostics "tools". No one is (should be) suggesting that you use COUNT in the SUMPRODUCT formula.

(Although COUNTIFS would be a better solution, eventually, with some rewrites to avoid MONTH(F2:F9999).)

-----

FYI, while jasonb75's diagnostic test was well-intentioned, it was superfluous. A quick test would demonstrate that MONTH(F2) returns #VALUE if F2 is text that Excel cannot interpret as numeric (including dates). So the SUMPRODUCT formula would return #VALUE instead of zero.

 

[Kirbstomp1287]

In my yearly worksheet the COUNTIF function works properly without looking for the month. So If I could get this to work while also looking for the month that would be perfect.

=COUNTIFS(D2:D9999,AB4,J2:J9999,"Forecast",AP2:AP9999,"Met DLT")

 

[joeu2004]

If I could get this to work while also looking for the month that would be perfect.
=COUNTIFS(D2:D9999,AB4,J2:J9999,"Forecast",AP2:AP9999,"Met DLT")

Sorry, I miswrote. Originally, I intended to note that we could __not__ use COUNTIFS to cover the MONTH requirement. At the last moment (or as an afterthought), I thought we could.

Well, we can, but only if all of the dates in D2:D9999 are in the same year. Are they? If so:

=COUNTIFS(D2:D9999, AB4, J2:J9999, "Forecast", AP2:AP9999, "Met DLT", F2:F9999, ">=" & DATE(YEAR(D2),6,1), F2:F9999, "<=" & DATE(YEAR(D2),6,31))

If you want to copy the formula down a column, change D2 to $D$2. But you should also change D2:D9999 to $D$2:$D$9999, J2:J9999 to $J$2:$J$9999, etc.

In any case, I would expect the same result that you get with SUMPRODUCT: zero. In other words, the COUNTIFS rewrite should not correct the problem, whatever it might be; it is simply more efficient.

Again, it would help if you attached an example Excel file that demonstrates the problem (with SUMPRODUCT) -- if that feature does indeed work now in this forum.

(In another discussion, a user referred to an attachment, but I failed to see it. It could be due to my unfamiliarity with the new forum feature. Or it could be because the feature is not "fully cooked" yet. TBD.)

 

[joeu2004]

Again, it would help if you attached an example Excel file that demonstrates the problem (with SUMPRODUCT) -- if that feature does indeed work now in this forum.

My bad! A forum moderator informed that that is not the purpose of the Upload button. We still cannot attach files in this forum.

So, I suggest that you upload the example Excel file that demonstrates the problem to a file-sharing website, and post the download URL in a response here. I like box.net/files.

Caveat: Some forum participants object to that because they cannot or will not download external files. But as I said, the devil might be in details that we cannot see if you simply post the data and formulas.

 

[jasonb75]

A quick test would demonstrate that MONTH(F2) returns #VALUE if F2 is text that Excel cannot interpret as numeric (including dates). So the SUMPRODUCT formula would return #VALUE instead of zero.

I hadn't thought of that, but now you have pointed it out, I'm going to stand by the other thing that I pointed out in my earlier reply.

one of the other columns is not an exact match for the criteria specified.

For clarity, as the way that I phrased it earlier could be misleading.

If the dates are valid then that means that there can be no rows of data where all 4 specified criteria match.

 

[Rick Rothstein]

Original message is cross-posted here...

SUMPRODUCT with month and other criteria.

I am trying to use a SUMPRODUCT function with the MONTH function to get a monthly count of specific data. Essentially I need the function to look for the month (formatted: 1/25/19), then the initials from box AB4 in column D, then the text "Forecast" from column J to get a count of text "Met...

www.excelforum.com