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")