Forum Discussion

littlevillage's avatar
littlevillage
Iron Contributor
Jan 02, 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
)
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
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