Hi all,
Is there a less volatile alternative to INDIRECT(Lists!J1) to retrieve the text from cell Lists!J1 in the COUNTIFS formula below?
=((COUNTIFS(INDIRECT(Lists!J1),"1",RAutumn,"EXS")+(COUNTIFS(INDIRECT(Lists!J1),"1",RAutumn,"GDS"))))/COUNTIF(INDIRECT(Lists!J1),"1")
Many thanks.
Why is INDIRECT needed here? What is contained in J1 ? Posting a small sample would help? (Go advanced - Manage attachments)
Thanks for replying.
I've tried cutting it down but it's still a bit large to upload.
Lists!J1 is:
=INDEX(Lists!F3:G29,(MATCH(ANALYSIS!N2,Lists!F3:F29,0)),2)
I want it to see what's in the drop-down list on ANALYSIS!N2, pull the corresponding field name from the list (Lists!G3:G29) and use that field name in the formula.
Hi All
if you have the infinite patience to list all ranges involved in your calculations, you could try to exploit SUMPRODUCT +INDEX(CHOOSE
Example: a countif of "ciaos" in the second column of the range choosed in the first argument
=SUMPRODUCT(--(INDEX(CHOOSE(number from your MATCH,List1!A1:B10,List2!A1:B10,List3!A1:B10),,2)="ciao"))
CHOOSE function should accept more than 200 arguments (differente ranges) to deliver to INDEX
Please forgive my early morning English
Last edited by canapone; 03-30-2019 at 05:05 AM.
-----------------------------------------------------
At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.
Please, mark your thread [SOLVED] if you received your answer.
Your English is great. It took me a while to figure out how this could work. I'll try it. Many thanks.
(Never mind
Last edited by Edward2200; 03-30-2019 at 02:10 PM.
Ciao,
you could substitute INDIRECT segments with
INDEX(Data!$A$4:$CW$1048576,,MATCH($N$2,Data!A$3:CW$3,0))
Please Login or Register to view this content.
Hope it helps
Last edited by canapone; 04-02-2019 at 01:56 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks