Forum Discussion

littlevillage's avatar
littlevillage
Iron Contributor
Jan 03, 2023
Solved

Combinating of sumifs and countifs.

Hi,

I Countifs with criterias "BCT", "LT", "232A01"  (1).

Sumifs of sales for that criterias( 2).

The expected result:= (2)/(1)

Hope for your help

Thank you.

 

 

 

excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Jan 03, 2023

    Detlef_Lewin 

    It looks like we need to check Position in the current row to receive expected result (row at the bottom)

    =IF(
    OR([@Position] = {"BCT", "LT"}),
    SUM(SUMIFS([Sales], [ID], [@ID], [Position], {"BCT", "LT"})) /
        SUM(COUNTIFS([ID], [@ID], [Position], {"BCT", "LT"})),
    0
)

7 Replies

Sort By
    • SergeiBaklan's avatar
      SergeiBaklan
      MVP
      Jan 03, 2023

      Detlef_Lewin 

      It looks like we need to check Position in the current row to receive expected result (row at the bottom)

      =IF(
    OR([@Position] = {"BCT", "LT"}),
    SUM(SUMIFS([Sales], [ID], [@ID], [Position], {"BCT", "LT"})) /
        SUM(COUNTIFS([ID], [@ID], [Position], {"BCT", "LT"})),
    0
)

Resources