Hi There,
I tried few times and see no issue in my formula for sumproduct. Can anybody help me. It looks a silly mistake but clearly I'm not looking properly
=SUMPRODUCT(--('raw data pcn sidem'!$P$1:$P$54629=F11),--('raw data pcn sidem'!$L$1:$L$54629='reps summary sheet'!Q10))
What I want is the first to look for a list and compare the name against in a main sheet( where result is displayed) and similar in the next part and just give me a total count..I'm using as array formula..
Please help!
No need to use as an ARRAY formula. Just click ENTER to confirm the formula.Originally Posted by nicci113
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
not working that way either
Looks OK, assuming that F11 is on the active sheet (the same sheet as the formula) and the sheet references are correct for the other ranges and cells.
When you say, "not working", what does that mean? We probably need to see a sample workbook to check the actual data.
SUMPRODUCT works with arrays but it is NOT an Array Formula, that is, you do NOT need to use Ctrl-Shift-Enter to commit it.
Regards, TMS
Last edited by TMS; 06-02-2014 at 07:05 AM.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Before you upload a small sample workbook, give a try to this.
Formula:
Please Login or Register to view this content.
I believe you are missing one more parameter in the SUMPRODUCT
If my assistance has helped, there is a reputation icon * on the left hand corner below the post - you can show your appreciation to the user who has helped in resolving your requirement.
If your requirement has been solved please mark your thread as Solved.
In the menu bar above the very first post, select Thread Tools, then select "Mark this thread as Solved".
Kindly use [FORMULA] or [CODE] tags when posting your code.
Regards,
Sarang
I believe you are missing one more parameter in the SUMPRODUCT
What would that be?
attached!
what I m doing is counting the nyumber of notice in sheet 1 against number of people in sheet 2 and getting a total..
hope it not diffucult to understand
Hi,
The two sheet1 ranges need to be the same number of rows. In addition the $Q$10 reference should be relative as far as the column ref is concerned. i.e.
Q11: copied across & down
Formula:Please Login or Register to view this content.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Formula:Please Login or Register to view this content.
aww.. i knew its sillly saved a time bloody spending 1 hr on this..
Thanks a lot all of you and really apperciate your assistance.. S
See the attached. Usually better to keep the structure the same but hopefully you can work with the attached updated example.
Thanks yes, but the another issue ws that my other sheet had N/a and it was also causing the issue just updated. Sometime the wee things are more of a pain
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks