Forum Discussion

Rey721's avatar
Rey721
Copper Contributor
Sep 30, 2024
Solved

Help with formula involving multiple columns

Hi  I dont know if this is been addressed in the past or if someone could help me. I am trying to sum or count the times one item shows in a list but has values between one amount and the other. Fo...
excel
Formulas and Functions
  • mathetes's avatar
    mathetes
    Sep 30, 2024

    Rey721 

     

    This formula in column F produces a count of 10 for instances between 0 and 10

     

     

    =COUNTA(FILTER(C2:C16,(C2:C16>0)*(C2:C16<=10)*(B2:B16="Market Area 1")))

     

     

    In column G, this one gave a count of 5, for instances between 11 and 30

     

     

    =COUNTA(FILTER(C2:C16,(C2:C16>=11)*(C2:C16<=30)*(B2:B16="Market Area 1")))

     

     

    If instead of a count, you prefer a SUM, just change the COUNTA to SUM. Since these are numbers being counted, you could also use COUNT rather than COUNTA.

Lorenzo's avatar
Lorenzo
Silver Contributor
Sep 30, 2024

Hi Rey721 

 

With (almost) any version of Excel, assuming data in Table1:

 

in G4:

=COUNTIFS(
  Table1[Market Area], F4,
  Table1[Completed Calls], ">=" & 0,
  Table1[Completed Calls], "<=" & 10
)

in H4:

=SUMIFS(
  Table1[Completed Calls],
  Table1[Market Area], F4,
  Table1[Completed Calls], ">=" & 0,
  Table1[Completed Calls], "<=" & 10
)

 

For >=11 & <= 30 same as above, just change the numbers (Implemented in attached file)