Forum Discussion

James Paparone's avatar
James Paparone
Copper Contributor
Apr 19, 2018

Excel Formula

I am trying to write a formula that will add up a symbol (in my case an x) that is in the same cell over several sheets in the same workbook and enter the total on an another sheet. For example:

sheet 1 cell D5 is x, sheet 2 D5 is x, sheet 3 D5 is blank, sheet 4 D5 is x, then sheet 5 D5 should equal 3. 

I think I should use COUNTIF; but I am having trouble getting the formula to work. 

Any help would be greatly appreciated

 

Jim

 

 

excel
  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    Apr 19, 2018

    Hi James,

     

    This can be done using this formula:

    =SUMPRODUCT(COUNTIF(INDIRECT("'"&{"Sheet1";"Sheet2";"Sheet3";"Sheet4"}&"'!D5"),"x"))

     

    I got it from here.

Resources