Forum Discussion

SharonMc5's avatar
SharonMc5
Copper Contributor
Sep 22, 2023
Solved

Summing an area with one criteria on multiple rows

I have a spreadsheet and trying to sum all of the rows and columns that match the criteria in the left column.  I am looking for the sum of Columns F, G, H, I, J that match "1".  What function can help me with this?  Thanks in advance

 

Criteria FGHIJSum I am looking for
1            500           100           200           600           700 
1            500           100           200           600           700 
1            500           100           200           600           700 
1            500           100           200           600           700 
1            500           100           200           600           700              10,500
2            500           100           200           600           700 
2            500           100           200           600           700 
2            500           100           200           600           700 
2            500           100           200           600           700 
2            500           100           200           600           700              10,500
3            500           100           200           600           700 
3            500           100           200           600           700                 4,200
4            500           100           200           600           700 
4            500           100           200           600           700 
4            500           100           200           600           700                 6,300
        
        
12500This is the result I am getting   
excel
Formulas and Functions

7 Replies

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Sep 22, 2023

    SharonMc5 

    Riny_van_Eekelen 's solution is succinct and effective.

    As a 365 user, I chose to take the solution as step further.

    = LET(
    SumIfλ,  LAMBDA(v, SUM(FILTER(array, criterion=v))),
    critVal, UNIQUE(criterion),
    totals,  MAP(critVal, SumIfλ),
    HSTACK(critVal, totals)
  )

    The main disadvantage is that it is harder to understand and is longer.  However, it identifies distinct criterial without user intervention and then generates totals for each criterion.  The criteria are stacked along with the relevant totals, so there is more automation.

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Sep 23, 2023

      PeterBartholomew1 

      And Python in Excel for the collection

      dfc = xl("A1:A16", headers=True)
dfd = xl("F1:J16", headers=True)
dfc.assign(Total = dfd.sum(axis =1)) \
   .groupby("Criterion", as_index=False).sum()
    • SharonMc5's avatar
      SharonMc5
      Copper Contributor
      Sep 22, 2023
      Thank you so much! I have to admit I don't understand the equation. What is the purpose of the two hyphens?
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Sep 22, 2023

        SharonMc5 

        (A2:A16=A19) returns a series of TRUE/FALSE values: TRUE for each cell in A2:A16 that equals A19, FALSE for all other cells in A2:A16.

        The - in -(A2:A16=A19) is a minus sign. It forces Excel to convert the result to numbers: TRUE is treated as 1 and FALSE as 0, so you now have a series of -1 / 0 values.

        The second minus sign converts the -1 values to +1. So --(A2:A16=A19) is an array of 1 / 0 values and those can be used in MMULT.

Resources