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.

 

[jkpieterse]

Sounds to me like you'd be better off using a pivot table for a problem like this?

 

[RoryA]

You've got strange quotes in the first formula which is probably why you get #NAME. Use this one:

=SUMPRODUCT(--('Sheet1'!$U2:$U600 = "Y")*(MONTH('Sheet1'!$K2:$K600)=10))

 

[carabale]

You've got strange quotes in the first formula which is probably why you get #NAME. Use this one:

=SUMPRODUCT(--('Sheet1'!$U2:$U600 = "Y")*(MONTH('Sheet1'!$K2:$K600)=10))

The {} referes to the characters on the screen after I entered Ctrl-Shift. Without them I get this message #VALUE!

 

[carabale]

Sounds to me like you'd be better off using a pivot table for a problem like this?

Not very familiar with Pivot Tables and when I say not very I mean not at all...

 

[RoryA]

I'm not talking about the curly brackets, I'm talking about the quotes (highlighted below in your original version):
=SUMPRODUCT(--('Sheet1'!$U2:$U600 = “Y”)*(MONTH('Sheet1'!$K2:$K600)=10))

 

[carabale]

I'm not talking about the curly brackets, I'm talking about the quotes (highlighted below in your original version):
=SUMPRODUCT(--('Sheet1'!$U2:$U600 = “Y”)*(MONTH('Sheet1'!$K2:$K600)=10))

Like I said, I put it together from similar formulas I found on this and other sites. If is wrong, what will be right?

The U column has a formula that results in a value of Y or N.
=IF(AND(AC65<>"",AL65>TODAY()+365),"Y",IF(AND(AD65<>"",AL65>TODAY()+365),"Y",IF(AND(AA65<>"",AB65<>""),"Y",IF(AND(AG65<>"",AL65>TODAY()+365),"Y",IF(AND(AH65<>"",AI65<>""),"Y","N")))))

Since I am working with text I used " ".

 

[RoryA]

I don't think you understand. The quotes in the formula you posted are not regular quotes. You have “Y” and not "Y" (examine them carefully ).

 

[carabale]

I don't think you understand. The quotes in the formula you posted are not regular quotes. You have “Y” and not "Y" (examine them carefully ).

Oh! I get it.

I changed it but same result...

Thank you so much for your patience with me. I reall appreciate it.

 

[RoryA]

Unless you're using a non-English version of Excel, the only reason I could see for getting a # name error would be if there are #NAME errors in the data that the formula is looking at.