Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct help with a difference
hi All,
i understand how sumproduct works but i want to return the column number of the first true(1) citriea within the sumproduct function. see below example Result row 2 4 6 7 5 6 6 (range is a2:a7) Data row 2 3 4 3 3 4 2 (range is b2:b7) =sumproduct(--(b2:b7=3),--(a2:a7)) this formula will return =sumproduct({0,1,0,1,1,0,0}, {2,4,6,7,5,6,6}) and the answer of 16 what i want to do is work out formula that will return the only the 3rd (1(True) within the sumproduct function) value therefore the answer should be 7. any thoughts?? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct help with a difference
sorry guys, i want the formula linked to cell c2, if i change c3 to 1 c2 will return 4, if i change c3 to 2 c2 should show 7 and if i change c3 to 3 c2 should show 5. hope it makes sense |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct help with a difference
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct help with a difference
Your data as originally posted is assumed in A2:B8, viz:
2 2 4 3 6 4 7 3 5 3 6 4 6 2 The input cell for the variable is C3, eg: 1, 2 , 3 Place this in C2, array-enter the formula, ie press CTRL+SHIFT+ENTER to confirm the formula: =INDEX(A2:A8,SMALL(IF((B2:B8=3)*ROW(1:7)<0,ROW(1: 7)),C3)) C2 will return the results that you seek, as described below -- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:365 Subscribers:65 xdemechanik --- wrote in message ... i want the formula linked to cell c2, if i change c3 to 1 c2 will return 4, if i change c3 to 2 c2 should show 7 and if i change c3 to 3 c2 should show 5. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct help with a difference
Max wrote:
Your data as originally posted is assumed in A2:B8, viz: 2 2 4 3 6 4 7 3 5 3 6 4 6 2 The input cell for the variable is C3, eg: 1, 2 , 3 Place this in C2, array-enter the formula, ie press CTRL+SHIFT+ENTER to confirm the formula: =INDEX(A2:A8,SMALL(IF((B2:B8=3)*ROW(1:7)<0,ROW(1: 7)),C3)) C2 will return the results that you seek, as described below I was hoping someone would post an array version because I could not get my head around it (though it makes perfect sense now). It looks like this could be simplified a little: =INDEX(A2:A8,SMALL(IF((B2:B8=3)*ROW(1:7),ROW(1:7)) ,C3)) Nice work! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct help with a difference
Thanks for the compliments, and the refinement!
-- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:365 Subscribers:65 xdemechanik --- "smartin" wrote I was hoping someone would post an array version because I could not get my head around it (though it makes perfect sense now). It looks like this could be simplified a little: =INDEX(A2:A8,SMALL(IF((B2:B8=3)*ROW(1:7),ROW(1:7)) ,C3)) Nice work! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
subtract the time difference from another time difference | Excel Discussion (Misc queries) | |||
Sumproduct against worksheet vs named range- any speed difference? | Excel Worksheet Functions | |||
sumproduct forumla, but I want difference instead of sum | Excel Worksheet Functions | |||
charting a difference of 2 columns' w/o adding a difference column | Charts and Charting in Excel |