SOLVED

After an IF statement , making 1 or 0, I need to add up only certain rows to get the total 1 count

%3CLINGO-SUB%20id%3D%22lingo-sub-2513262%22%20slang%3D%22en-US%22%3EAfter%20an%20IF%20statement%20%2C%20making%201%20or%200%2C%20I%20need%20to%20add%20up%20only%20certain%20rows%20to%20get%20the%20total%201%20count%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2513262%22%20slang%3D%22en-US%22%3E%3CP%3EI%20attached%20the%20file%2C%20the%20GOLD%20column%20is%20what%20I%20need%20it%20to%20look%20like%2C%26nbsp%3B%20I%20need%20it%20to%20add%20up%20all%20of%20the%201s%20that%20fit%20in%20the%20category%20of%20the%20string%20(Sun)%20in%20a2%20%2C%20and%20populate%20all%20of%20them%20that%20have%20the%201%20to%20the%20left%2C%20and%20are%20underneath%20A2%2C%20then%20when%20we%20get%20to%20a44%20(Abella)%20need%20it%20to%20start%20over%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2513262%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2513314%22%20slang%3D%22en-US%22%3ERe%3A%20After%20an%20IF%20statement%20%2C%20making%201%20or%200%2C%20I%20need%20to%20add%20up%20only%20certain%20rows%20to%20get%20the%20total%201%20cou%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2513314%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1094529%22%20target%3D%22_blank%22%3E%40bdog2021%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20G2%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(ISTEXT(A2)%2C0%2CIF(ISTEXT(A1)%2CSUM(OFFSET(F2%2C0%2C0%2CMATCH(TRUE%2CINDEX(E2%3AE%241000%3D%22%22%2C)%2C0)%2C1))%2CG1))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EFill%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2513429%22%20slang%3D%22en-US%22%3ERe%3A%20After%20an%20IF%20statement%20%2C%20making%201%20or%200%2C%20I%20need%20to%20add%20up%20only%20certain%20rows%20to%20get%20the%20total%201%20cou%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2513429%22%20slang%3D%22en-US%22%3Ethank%20you%20got%20it%20!%3C%2FLINGO-BODY%3E
New Contributor

I attached the file, the GOLD column is what I need it to look like,  I need it to add up all of the 1s that fit in the category of the string (Sun) in a2 , and populate all of them that have the 1 to the left, and are underneath A2, then when we get to a44 (Abella) need it to start over

2 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

@bdog2021 

In G2:

=IF(ISTEXT(A2),0,IF(ISTEXT(A1),SUM(OFFSET(F2,0,0,MATCH(TRUE,INDEX(E2:E$1000="",),0),1)),G1))

Fill down.