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
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
Last edited by guerillaexcel; 04-16-2010 at 09:37 AM.
No.Am I doing something blatently stupid here?
Sumproduct does not work in 3D.
You'll need to do individual sums on each sheet and then do a 3D sum
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
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!
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
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
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.
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.
I'll post a new thread as the question has changed, the original question has been answered, thanks guys!
Last edited by guerillaexcel; 04-16-2010 at 09:37 AM. Reason: Solved
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks