# Tearing My Hair Out! - SUMPRODUCT trouble!

#### I'm rubbish with Excel

##### Board Regular
This is starting to drive me insane:

I have the following formula in 9 cells within a column. The formula checks another worksheet (Called "Stats") for month (Column C) and staff name (Column P) against the staff name in the current worksheet next to the formula.

=SUMPRODUCT(('[Redraws XL.xls]Stats'!\$P\$1:\$P\$65000=A6)*('[Redraws XL.xls]Stats'!\$C\$1:\$C\$65000=\$A\$1))*('[Redraws XL.xls]Stats'!\$D\$1:\$D\$65000='Year Totals'!\$A\$1)

In other words, this formula is in cell B6, checking "Stats!" for any work done by "Heather Burgis" (in cell A6) in January (in cell A1)

For some bizarre reason, the formula only works on one cell (B10), even though I've dragged the formula down all cells and have the same name in the relevent "A" Column next to it (as a test.)

Does anyone have any idea what I'm doing wrong?

Hope this makes sense.

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Just a guess,

=SUMPRODUCT(--('[Redraws XL.xls]Stats'!\$P\$1:\$P\$65000=A6),--('[Redraws XL.xls]Stats'!\$C\$1:\$C\$65000=\$A\$1),--('[Redraws XL.xls]Stats'!\$D\$1:\$D\$65000='Year Totals'!\$A\$1))

misplaced parenthesis. That's a lot of rows you're checking!

You're a genius! ray:

Thanks. You should be called the Brain from Maui!

Replies
6
Views
361
Replies
0
Views
243
Replies
1
Views
224
Replies
1
Views
111
Replies
7
Views
297

1,203,625
Messages
6,056,398
Members
444,862
Latest member
more_resource23

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