Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nesting Index & Countif
=INDEX(COUNTIF('PAR CALC.'!A3:A133,B3),'PAR
CALC.'!C3:C133,MATCH(Answers!L4,'PAR CALC.'!AV3:AV133,0)) Is this the proper way to nest these two functions? It returns a #REF! error, but is it due to the formula or the calculation? Thanks. M.A.Tyler |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nesting Index & Countif
Maybe you should explain hat you are trying to do instead, that formula make
no sense to me, why are you even trying to include COUNTIF? -- Regards, Peo Sjoblom "M.A.Tyler" <Great Lakes State wrote in message ... =INDEX(COUNTIF('PAR CALC.'!A3:A133,B3),'PAR CALC.'!C3:C133,MATCH(Answers!L4,'PAR CALC.'!AV3:AV133,0)) Is this the proper way to nest these two functions? It returns a #REF! error, but is it due to the formula or the calculation? Thanks. M.A.Tyler |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nesting Index & Countif
My apologies for the confusion, I'm a bit of a novice.
There are two worksheets, "Answers" and "PAR CALC." I would like to populate Answers D4 with the data from PAR CALC. C3:C133, that matches Answers B3, There is however a second match criteria, PAR CALC. AV3:AV133 needs to match Answers L4. I thought if I could somehow isolate the matches from the second criteria first, count only if (Countif). Then I could simply do an Index Match for the first criteria. This way I would have just one formula, perhaps thats not possible? Thanks for your help, and patience. Mike. "Peo Sjoblom" wrote: Maybe you should explain hat you are trying to do instead, that formula make no sense to me, why are you even trying to include COUNTIF? -- Regards, Peo Sjoblom "M.A.Tyler" <Great Lakes State wrote in message ... =INDEX(COUNTIF('PAR CALC.'!A3:A133,B3),'PAR CALC.'!C3:C133,MATCH(Answers!L4,'PAR CALC.'!AV3:AV133,0)) Is this the proper way to nest these two functions? It returns a #REF! error, but is it due to the formula or the calculation? Thanks. M.A.Tyler |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nesting Index & Countif
Try
=INDEX(C3:C133,MATCH(1,(A3:A133=B3)*(AV3:AV133=L4) ,0)) I removed your sheet names to reduce the clutter but you should see what to do This is an array formula and needs to be entered with ctrl + shift & enter as opposed to just enter -- Regards, Peo Sjoblom "M.A.Tyler" <Great Lakes State wrote in message ... My apologies for the confusion, I'm a bit of a novice. There are two worksheets, "Answers" and "PAR CALC." I would like to populate Answers D4 with the data from PAR CALC. C3:C133, that matches Answers B3, There is however a second match criteria, PAR CALC. AV3:AV133 needs to match Answers L4. I thought if I could somehow isolate the matches from the second criteria first, count only if (Countif). Then I could simply do an Index Match for the first criteria. This way I would have just one formula, perhaps thats not possible? Thanks for your help, and patience. Mike. "Peo Sjoblom" wrote: Maybe you should explain hat you are trying to do instead, that formula make no sense to me, why are you even trying to include COUNTIF? -- Regards, Peo Sjoblom "M.A.Tyler" <Great Lakes State wrote in message ... =INDEX(COUNTIF('PAR CALC.'!A3:A133,B3),'PAR CALC.'!C3:C133,MATCH(Answers!L4,'PAR CALC.'!AV3:AV133,0)) Is this the proper way to nest these two functions? It returns a #REF! error, but is it due to the formula or the calculation? Thanks. M.A.Tyler |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nesting Index & Countif
Peo,
Thanks for the help and patience, it worked great first try! "Peo Sjoblom" wrote: Try =INDEX(C3:C133,MATCH(1,(A3:A133=B3)*(AV3:AV133=L4) ,0)) I removed your sheet names to reduce the clutter but you should see what to do This is an array formula and needs to be entered with ctrl + shift & enter as opposed to just enter -- Regards, Peo Sjoblom "M.A.Tyler" <Great Lakes State wrote in message ... My apologies for the confusion, I'm a bit of a novice. There are two worksheets, "Answers" and "PAR CALC." I would like to populate Answers D4 with the data from PAR CALC. C3:C133, that matches Answers B3, There is however a second match criteria, PAR CALC. AV3:AV133 needs to match Answers L4. I thought if I could somehow isolate the matches from the second criteria first, count only if (Countif). Then I could simply do an Index Match for the first criteria. This way I would have just one formula, perhaps thats not possible? Thanks for your help, and patience. Mike. "Peo Sjoblom" wrote: Maybe you should explain hat you are trying to do instead, that formula make no sense to me, why are you even trying to include COUNTIF? -- Regards, Peo Sjoblom "M.A.Tyler" <Great Lakes State wrote in message ... =INDEX(COUNTIF('PAR CALC.'!A3:A133,B3),'PAR CALC.'!C3:C133,MATCH(Answers!L4,'PAR CALC.'!AV3:AV133,0)) Is this the proper way to nest these two functions? It returns a #REF! error, but is it due to the formula or the calculation? Thanks. M.A.Tyler |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nesting COUNTIF | Excel Discussion (Misc queries) | |||
If and CountIF Nesting | Excel Worksheet Functions | |||
Nesting Index and Match Functions | Excel Worksheet Functions | |||
nesting with countif | Excel Discussion (Misc queries) | |||
Nesting formulae with COUNTIF | Excel Discussion (Misc queries) |