SUMPRODUCT with multiple criteria

[carabale]

I have a table like the one below, which I update manually and I am trying to automate.

MONTH VALID TOTIM %ID READY
Oct-14 8 10 80%
Nov-14 3 9 33%
Dec-14 7 15 47%
Jan-15 5 16 31%
Feb-15 2 4 50%

On Sheet1, I want to count all "Y" for the month of October and count all records with a date in October so I can calculate the percentage.

I have this formula under VALID
{=SUMPRODUCT(--('Sheet1'!$U2:$U600 = “Y”)*(MONTH('Sheet1'!$K2:$K600)=10))}

And this one under TOTIM
=SUMPRODUCT(--('Sheet1'!$U2:$U600)*(MONTH('Sheet1'!$K2:$K600)=10))

But I only get #NAME? Needless to say, I do not know what I am doing. I pretty much search the this and other sites and kind a put things together.

Hopefully you guys show the the right direction.

PS: the data in worksheet Sheet1 and the table is in worksheet STATS

As always, thanks in advance.

 

[Joe4]

Re: Count Occurences Which Meet a Criteria During a Particular Month

Very odd... I cannot get it to work either. It returns the #VALUE error for me also.
I have narrowed it down to the facts that it does not seem to like the use of the MONTH and YEAR functions in the SUMPRODUCT formula.
I will keep messing around with it, and see what I can find.

 

[carabale]

Re: Count Occurences Which Meet a Criteria During a Particular Month

I cheated a little bit. I added a column in Sheet1 and with a formula =K2 and custom formatted in to mmm YYYYY.
In the STAT worksheet I entered this formula to count for records:

=SUMPRODUCT(--(TEXT(Sheet1!K:K,"mmm yy")="Oct 14"))

I had to manually changed every month but I can live with that.

Now, if can count the "Y"s I will be cooking.

Thank you for hanging in there.

 

[Joe4]

Re: Count Occurences Which Meet a Criteria During a Particular Month

I actually figured out the issue I was having. I had the formulas extend down past the end of my data.
So this returned the #VALUE error:

=SUMPRODUCT((MONTH(K2:K600)=10)*(YEAR(K2:K600)=2014)*U2:U600="Y")

However, when I changed the format slightly to, this, it worked, even with blank rows at the bottom of my data:

=SUMPRODUCT(--(MONTH(K2:K600)=10),--(YEAR(K2:K600)=2014),--(U2:U600="Y"))

 

[carabale]

Re: Count Occurences Which Meet a Criteria During a Particular Month

It did not worked. It should had worked but it did not. I will read up some more and try countif or something.

Again, thank you for your patience and effort.

 

[Joe4]

Re: Count Occurences Which Meet a Criteria During a Particular Month

Let's try debugging.

First locate a record that should be counted. Let's say it is row 4. Then enter these formulas anywhere on your sheet, and see what each one returns:
=MONTH(K4)
=YEAR(K4)
=U4="Y"

 

[carabale]

Re: Count Occurences Which Meet a Criteria During a Particular Month

Thank you for not giving up.

It return the following for each equation:

9
2014
TRUE

 

[RoryA]

Re: Count Occurences Which Meet a Criteria During a Particular Month

I think you have some cells in column K returning "" from the formula. You'd need to handle those:
=SUMPRODUCT(IF(ISNUMBER(K2:K600),(MONTH(K2:K600)=10)*(YEAR(K2:K600)=2014),0)*U2:U600="Y")
array entered with Ctrl+Shift+Enter. This will probably calculate slowly if you have a lot of them, so JKP was probably right about using a pivot table!

 

[carabale]

Re: Count Occurences Which Meet a Criteria During a Particular Month

Thank you for your answer. I still get the #VALUE! so I will check some website and see what I can learn about Pivot Tables and report back. Thank you everyone for their input and effort.

 

[Joe4]

Re: Count Occurences Which Meet a Criteria During a Particular Month

One other thing you may want to try:

Where exactly does your data end? Does it really go down to row 600, or is that just padded with extra rows?
Just for kicks, trying changing the formula to end at the exact row your data ends, and see if that works.
If that does not, what happens if you try that formula to end at row 10?
What I am thinking is that maybe you have an odd/unexpected entry somewhere in your data that is causing the issue.

 

[carabale]

Re: Count Occurences Which Meet a Criteria During a Particular Month

The data will vary between 470 and 510 records. I did notice there is always a Value in U column of "N" even if no record and K column is blank.