# Sum first n cells

#### raikks

##### New Member
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)

#### Attachments

• 4.8 KB Views: 14

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

#### J.Ty.

##### Well-known Member
Hi,

Welcome to the Board!

`=SUMIF(A2:INDEX(A:A,SMALL(IF(A:A="yes",1,1000)*ROW(A:A),3)),"Yes",C2:INDEX(C:C,SMALL(IF(A:A="yes",1,1000)*ROW(A:A),3)))`
You have to confirm it using Ctrl-Shift-Enter.

• raikks

#### Peter_SSs

##### MrExcel MVP, Moderator
First one:

=SUMIF(A2:INDEX(A2:A10,AGGREGATE(15,6,(ROW(A2:A10)-ROW(A2)+1)/(A2:A10="yes"),3)),"yes",C2:C10)

Have to go. Will come back to the second one later if not resolved.

#### raikks

##### New Member
Hi,

Welcome to the Board!

`=SUMIF(A2:INDEX(A:A,SMALL(IF(A:A="yes",1,1000)*ROW(A:A),3)),"Yes",C2:INDEX(C:C,SMALL(IF(A:A="yes",1,1000)*ROW(A:A),3)))`
You have to confirm it using Ctrl-Shift-Enter.
Jesus - it works!
Thank you, sir!

#### raikks

##### New Member
First one:

=SUMIF(A2:INDEX(A2:A10,AGGREGATE(15,6,(ROW(A2:A10)-ROW(A2)+1)/(A2:A10="yes"),3)),"yes",C2:C10)

Have to go. Will come back to the second one later if not resolved.
Thank you, it works!
Would very much appreciate a solution for the second one as well.

#### J.Ty.

##### Well-known Member
Thank you, it works!
Would very much appreciate a solution for the second one as well.
Does the double "Yes" in the first row count as 2 or as 1 "Yes"?

#### raikks

##### New Member
I think it counts as 1 ``yes''.
I'll attach another pic to emphasize what I really want to sum up.
Let me know if it's clear.

#### Attachments

• 5.6 KB Views: 7

#### Peter_SSs

##### MrExcel MVP, Moderator
Second one:

If you use a helper cell (G1 for me) with this formula:
=AGGREGATE(15,6,ROW(C2:C10)/((A2:A10="yes")*(B2:B10="yes")),3)
then
=SUMIFS(C2:INDEX(C:C,G1),A2:INDEX(A:A,G1),"yes",B2:INDEX(B:B,G1),"yes")

If you want without a helper then ..
=SUMIFS(C2:INDEX(C:C,AGGREGATE(15,6,ROW(C2:C10)/((A2:A10="yes")*(B2:B10="yes")),3)),A2:INDEX(A:A,AGGREGATE(15,6,ROW(C2:C10)/((A2:A10="yes")*(B2:B10="yes")),3)),"yes",B2:INDEX(B:B,AGGREGATE(15,6,ROW(C2:C10)/((A2:A10="yes")*(B2:B10="yes")),3)),"yes")

• raikks

#### J.Ty.

##### Well-known Member
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.

• raikks

#### raikks

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

1,089,515
Messages
5,408,740
Members
403,224
Latest member
rholmesa