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.

 

[Peter_SSs]

Size of the excel file exceeds 110 MB

Sorry, I wasn't clear enough. What I meant was: About how many rows of data?

 

[Mattia]

Good morning

=SUMPRODUCT((A$2:A$10="yes")*(C$2:C$10)*(ROW(B$2:B$10)<=SMALL((A$2:A$10<>"yes")*1000+ROW(A$2:A$10),3)))

=SUMPRODUCT((((A$2:A$10="yes")*($B$2:$B$10="yes"))*C$2:C$10)*(ROW(B$2:B$10)<=SMALL(((A$2:A$10<>"yes")+(B$2:B$10<>"yes"))*1000+ROW(A$2:A$10),3)))

 

[sandy666]

I think I'll stick w/ formulas, it's easier for me.

sure, happy formula-ing

 

[raikks]

Sorry, I wasn't clear enough. What I meant was: About how many rows of data?

150 rows for the sheet where i needed this formula

 

[Peter_SSs]

150 rows for the sheet where i needed this formula

In Excel terms that is a very small number so much better to have your formula only process a relatively small number of rows than whole columns where you would be dealing with over a million rows. So, for my formulas, something like these would easily cover it (allowing for data to row 500)

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

And for the second one, if you use the helper cell the helper cell formula in G1 would change to
=AGGREGATE(15,6,ROW(C2:C500)/((A2:A500="yes")*(B2:B500="yes")),3)
& the result formula stay the same as
=SUMIFS(C2:INDEX(C:C,G1),A2:INDEX(A:A,G1),"yes",B2:INDEX(B:B,G1),"yes")

Without the helper cell it would be
=SUMIFS(C2:INDEX(C:C,AGGREGATE(15,6,ROW(C2:C500)/((A2:A500="yes")*(B2:B500="yes")),3)),A2:INDEX(A:A,AGGREGATE(15,6,ROW(C2:C500)/((A2:A500="yes")*(B2:B500="yes")),3)),"yes",B2:INDEX(B:B,AGGREGATE(15,6,ROW(C2:C500)/((A2:A500="yes")*(B2:B500="yes")),3)),"yes")

 

[raikks]

I've chosen the solution w/ the helper - very easy