Forum Discussion

Josh Barton's avatar
Josh Barton
Copper Contributor
Mar 09, 2018
Solved

SUM cells from list IF associated cells have "1" or "0)

Dear experts,

I am trying to sum all cells that have an associated cell with 1 or 0. The association will change dependant on the position of the chain (ie it will be in same row or a row X above my cell of interest). In this attached example, for the 0 group I need to average C2:C10, C17:C25, C22:C30, C27:35 to get F2:F11. In reality each set will include 500 rows so I'm desperate for an IF formula.

I was thinking something like    =SUMIF(C2:C41,IF(ADDRESS(ROW()-X,COLUMN()-1),1," "))    may work, it returns a zero.

Can anyone shed light on my problem?

 

Many thanks,

Josh

Formulas & Functions
Need Help
  • Jamil's avatar
    Jamil
    Mar 09, 2018

    What is the version if your Excel?  if you have Excel 365 then you can use AVERAGEIF function.

     

    if not then you can use this formula =AVERAGE(IF($B$2:$B$41=1,$C$2:$C$41,""))

     

    remember to enter it with control shift enter because it is an array formula.

     

    for the other one simply change the =1 to =0

     

     

  • Jamil's avatar
    Jamil
    Bronze Contributor
    Mar 09, 2018

    What is the version if your Excel?  if you have Excel 365 then you can use AVERAGEIF function.

     

    if not then you can use this formula =AVERAGE(IF($B$2:$B$41=1,$C$2:$C$41,""))

     

    remember to enter it with control shift enter because it is an array formula.

     

    for the other one simply change the =1 to =0

     

     

Resources