Thank you as well J.Ty. for your help - formula works perfectly.OK, here you go:
=SUMIFS(C2:INDEX(C:C,SMALL(IF((A:A="yes")+(B:B="yes")=2,1,1000)*ROW(A:A),3)), A2:INDEX(A:A,SMALL(IF((A:A="yes")+(B:B="yes")=2,1,1000)*ROW(A:A),3)),"Yes", B2:INDEX(B:B,SMALL(IF((A:A="yes")+(B:B="yes")=2,1,1000)*ROW(A:A),3)),"Yes")
Again use Ctrl-Shift-Enter.
Not sure which formula(s) you are referring to or have ended up using but there is a significant difference in performance if your actual range is relatively small. For example, for the sample data provided, on my machine the formula in post #3 is approx 8,000 times faster than that from post #2.Thank you once again - extremely helpful for me this formula.
Didn't the OP already tell us in post 1?is that what you want to achieve?
=IF(AND(A2="yes",B2="yes"),1+D1,D1)
in D2 and fill all the way down=SUMIFS(C2:C10,A2:A10,"yes",B2:B10,"yes",D2:D10,"<=3")
to calculate what you want.Yes, i've checked the data and all the formulas you've sent me worked fine.just for fun, is that what you want to achieve?
componentl component2 total componentl component2 Sum yes yes 23 yes yes 121 no no 43 yes 88 no no 54 yes yes 23 yes no 42 yes yes 75 no yes 12 no yes 5 yes no 88
// DoubleYes
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Filter = Table.SelectRows(Source, each ([componentl] = "yes")),
Kept3 = Table.FirstN(Filter,3),
Group = Table.Group(Kept3, {"componentl"}, {{"Sum", each List.Sum([total]), type number}}),
Source2 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Filter2 = Table.SelectRows(Source2, each ([componentl] = "yes") and ([component2] = "yes")),
Kept32 = Table.FirstN(Filter2,3),
Group2 = Table.Group(Kept32, {"componentl", "component2"}, {{"Sum", each List.Sum([total]), type number}}),
Append = Table.Combine({Group2, Group})
in
Append
Thank you, Sandy - I think I'll stick w/ formulas, it's easier for me.I didn't send any formula but I can give you M-code for Power Query (Get&Transform) to get the result you Quoted
Code:// DoubleYes let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], Filter = Table.SelectRows(Source, each ([componentl] = "yes")), Kept3 = Table.FirstN(Filter,3), Group = Table.Group(Kept3, {"componentl"}, {{"Sum", each List.Sum([total]), type number}}), Source2 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], Filter2 = Table.SelectRows(Source2, each ([componentl] = "yes") and ([component2] = "yes")), Kept32 = Table.FirstN(Filter2,3), Group2 = Table.Group(Kept32, {"componentl", "component2"}, {{"Sum", each List.Sum([total]), type number}}), Append = Table.Combine({Group2, Group}) in Append
Can you give us a concept of what "quite large" is in relation to your data?