Array Formula: anyone can help me?
Hi,
This is the first time I place a comment in a forum. I have a big problem with an array formula, anyone can help me?
Attached is the file with the question.
Thanks!
Hi,
See attached sample ...
HTH
Thanks Jean,
The gender must be "lookup" from the first tab, I see you added 1 and 2 values in the column A. For example, "M" users can be the # 1,3,5,etc....the same with "F" users...
Sergio.
Hi Sergio,
Is the Users Tab complete or not ... ? you mention, 1,3,5, etc ... for M and may be 2,4,6, etc ... for F ...
Edited down.
Last edited by JBeaucaire; 12-12-2009 at 09:33 AM.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
yes, the users tab is only with some samples, but I will paste about 5,000 records (users).
So, in the formula there should be something like (initiatives!$D$2:$D$8= 'M or F' (Lookup from Users tab)....
Put this in C6 and then copy down:
=SUMPRODUCT(--(initiatives!$B$2:$B$8=$C$4),--(initiatives!$C$2:$C$8=$C$5),--(LOOKUP(initiatives!$D$2:$D$8,users!$A$2:$A$4,users!$B$2:$B$4)=B6))
Adjust the ranges to suit.
When you expand the USER list, be sure the first column stays sorted ascending like it is now.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Thanks for your reply JBeaucaire, I really don't understand why Users must be sorted...I try to unsort them and the the formula give a wrong result...Originally Posted by JBeaucaire
The LOOKUP function requires that the column A be sorted ascending. That's an Excel thing and it's not problematic. Keeping the list in order of 1,2,3, etc is common practice and easy to read.Thanks for your reply JBeaucaire, I really don't understand why Users must be sorted...I try to unsort them and the the formula give a wrong result...
My suggestion is to use that formula and keep the User list sorted by column A.
============
If there's some good reason to jumble the values on the user's list, then this alternate version will work:
=SUMPRODUCT((initiatives!$B$2:$B$8=$C$4)*(initiatives!$C$2:$C$8=$C$5)*(VLOOKUP(initiatives!$D$2:$D$8 ,users!$A$2:$B$4,2,FALSE)=B6))
...but I wouldn't jumble the values.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Thanks....with VLOOKUP seems not working good.....look the attached sample...the result must be M=1 and F=1.
Many and many thanks to all!
This won't work because VLOOKUP, unlike LOOKUP, can't return an array when fed an array as the lookup value.
If you don't have Users!A2:A4 sorted ascending then you could use this array formula
=SUM((initiatives!B$2:B$8=$C$4)*(initiatives!C$2:C$8=$C$5)*(initiatives!D$2:D$8<>"")*ISNUMBER(MATCH( initiatives!D$2:D$8,IF(users!B$2:B$4=B6,users!A$2:A$4,""),0)))
confirmed with CTRL+SHIFT+ENTER
And just to add the obvious - if you could calculate gender per transaction on initiatives sheet (via VLOOKUP) you could then revert to the more efficient COUNTIFS function and/or better yet use a Pivot Table to analyse the data ...
either/or would IMO prove beneficial long term (as I say, if viable).
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks