# SUMPRODUCT across worksheets

1. ## SUMPRODUCT across worksheets

I'm trying to get SUMPRODUCT to work across worksheets but am getting #REF
here is my formula:

=SUMPRODUCT(('MP 2010'!B2:B1312>=K1)*('MP 2010'!B2:B1312<=K2))

Am I doing something blatently stupid here?

Cheers

2. ## Re: SUMPRODUCT across worksheets

Am I doing something blatently stupid here?
No.

Sumproduct does not work in 3D.

You'll need to do individual sums on each sheet and then do a 3D sum

3. ## Re: SUMPRODUCT across worksheets

that’s a pity but it explains whats happening so thanks for your advice.

Ok, my next question is that Im counting a search term that falls between two dates. AI2 and AJ2 are my date range, in this instance between 01 jan 2010 and 31 jan 2010 and my search term is "inst".

I'd like to add a drop down box (validation) that lets me select February or March and will automatically change my results for that date range. Any ideas?
=SUMPRODUCT(B2:B2000=AI2)*(B2:B2000<=AJ2)*(G2:G2000="inst"))

Thanks again

4. ## Re: SUMPRODUCT across worksheets

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

5. ## Re: SUMPRODUCT across worksheets

Yes of course, here is a sample spreadsheet with my data.
I've updated my sample formula for the January feed

=SUMPRODUCT((\$B\$2:\$B\$2000>=Q2)*(\$B\$2:\$B\$2000<=R2)*(\$G\$2:\$G\$2000="inst"))

so to clarify, in I2, I'd like to be able to select February or March and have the figures update in L2, M2, and N2 respectively.

Cheers

6. ## Re: SUMPRODUCT across worksheets

Your formulas can be simplified it the data validation refers to a date value instead of text. The Sumproduct can then check for the month and the year of the date value. Also, you don't need to hard-code the texts if you refer the values in L1 to N1 in the sumproduct.

Effectively, you have only one formula, which you apply in L2 and copy right

=SUMPRODUCT(--(MONTH(\$B\$2:\$B\$2000)=MONTH(\$I\$2)),--(YEAR(\$B\$2:\$B\$2000)=YEAR(\$I\$2)),--(\$G\$2:\$G\$2000=L\$1))

see attached

hth

7. ## Re: SUMPRODUCT across worksheets

Teylyn,
Shouldn't it be possible to make sumproduct work in 3D by using named ranges? See attachment where i moved the "Term" column to Sheet2.

8. ## Re: SUMPRODUCT across worksheets

You are both very kind, thank you again for your help, either solution works just fine.
Can I make one final request

I also have a time column in column H and would like to return the time to complete for each search term. i.e in january "inst" took 40 hours, "chel" took 15 etc. I have attached the updated spreadsheet as an example.

9. ## Re: SUMPRODUCT across worksheets

I'll post a new thread as the question has changed, the original question has been answered, thanks guys!

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1