Sum first n cells

[raikks]

Hi - I need a formula that sums the first 3 cells of component1 that contains ``yes'' (result should be 88)
Also - I need to sum the first 3 cells of component1 & component2 that contains ``yes'' (result should be 121)
Thanks in advance.

 

[raikks]

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.

Thank you as well J.Ty. for your help - formula works perfectly.
Great community here

 

[sandy666]

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

 

[Peter_SSs]

Thank you once again - extremely helpful for me this formula.

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.
The difference between those in posts 8 & 9 is approximately 60 times.

is that what you want to achieve?

Didn't the OP already tell us in post 1?

 

[sandy666]

Didn't the OP already tell us in post 1?

no, because in post #12 both results are in the single table at once without different formulas

 

[J.Ty.]

If performance is a concern, then the following is probably still faster:

• Put 0 in D1
• Put =IF(AND(A2="yes",B2="yes"),1+D1,D1) in D2 and fill all the way down
• Use =SUMIFS(C2:C10,A2:A10,"yes",B2:B10,"yes",D2:D10,"<=3") to calculate what you want.

There is stll room for some further improvement, but you would probably see the difference only for extremely large data sizes.

 

[raikks]

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

Yes, i've checked the data and all the formulas you've sent me worked fine.
Performance is important, datasets i'm working with are quite large.
I appreciate a lot all your replies, very helpful.

 

[sandy666]

I didn't send any formula but I can give you M-code for Power Query (Get&Transform) to get the result you Quoted

// 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

 

[Peter_SSs]

datasets i'm working with are quite large.

Can you give us a concept of what "quite large" is in relation to your data?

 

[raikks]

I didn't send any formula but I can give you M-code for Power Query (Get&Transform) to get the result you Quoted

// 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.
But i appreciate your help.

 

[raikks]

Can you give us a concept of what "quite large" is in relation to your data?

Size of the excel file exceeds 110 MB