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.

 

[carabale]

Count Occurences Which Meet a Criteria During a Particular Month

I have tried SUMPRODUCT and COUNTIF but cannot come up with a formula that does what I want.

I have a data worksheet (Sheet1) and a Table in another worksheet (STATS)

I want it to count every instance there is a Y in column "U" for each month of the year. The Date is in column "K" 0/00/0000 format.

And I also need it to count all the records for that month so I can calculate the percentages.

Month Y TOT %
Oct 14 8 10 80%
Nov 14 4 8 50%

Remember there are in separate worksheets. Thank you in advance.

 

[C Moore]

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

=SUMPRODUCT((Month(Range of Column U)=Month of interest)*(Year(Range of column U)=Year of interest)*(Range of column U="Y")) should count all Ys, remove Ys condition to get count of month.

 

[carabale]

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

Your formula should work, but for some reason I am getting #VALUE! on both fields

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

K AND U columns contain formulas will that affect anything?

 

[carabale]

I do not have any errors on the data . I was given the following formulas by another member but I get #VALUE!

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

K AND U columns contain formulas will that affect anything?

 

[Joe4]

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

What are the formulas in column K and U?
Note, that is the formula in column K returns a Text value, Date functions will not work on it without converting it to a valid Date value (i.e. using the DateValue function in your Formula in column K).

 

[Joe4]

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

Note. I merged your two threads together.

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule #9 here: Forum Rules).

 

[carabale]

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

K = =if(a65="","",if(countif('otc50'!$a$1:$e$599,a65),vlookup(a65,'otc50'!$a$1:$e$599,3,false),""))

u = =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")))))

 

[carabale]

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

Sorry, in the first post I needed help with the SUMPRODUCT, but I reached a dead end. On the second post, I tried a wider approach; but you are right is all about the same problem. I just did not see it like that at the time.

 

[Joe4]

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

Pick a cell in column K that is returning a date.
Let's say it is cell K3. What does this formula return?
=ISNUMBER(K3)

If it returns FALSE, it means the values you are returning in your VLOOKUP function are actually String values and not Date values.
You may need to amend the formula like this:

[COLOR=#333333]=if(a65="","",if(countif('otc50'!$a$1:$e$599,a65),datevalue(vlookup(a65,'otc50'!$a$1:$e$599,3,false)),""))[/COLOR]

 

[carabale]

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

I placed =ISNUMBER(K3) in cell AZ3 and returned TRUE.