# SUMPRODUCT with multiple criteria

#### carabale

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

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

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

#### jkpieterse

##### Well-known Member
Sounds to me like you'd be better off using a pivot table for a problem like this?

#### RoryA

##### MrExcel MVP, Moderator
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

##### Board Regular
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

##### Board Regular
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

##### MrExcel MVP, Moderator
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

##### Board Regular
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.

Since I am working with text I used " ".

#### RoryA

##### MrExcel MVP, Moderator
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

##### Board Regular
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

##### MrExcel MVP, Moderator
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.

Replies
4
Views
266
Replies
5
Views
160
Replies
2
Views
387
Replies
1
Views
206
Replies
15
Views
304

1,191,683
Messages
5,987,990
Members
440,124
Latest member
dippy_egg

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back