Help with formula involving multiple columns

[Rey721]

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. For example, there are three columns: one has phone numbers, the other shows the area they belong to, and the third shows how many times that number has been used. I need to calculate, for example, how many calls are between 0 and 10 times for one area, one for calls between 11 and 30, etc. I appreciate the help; below is an example of the table.

Toll-Free Number	Market Area	Completed Calls			Total between 0 and 10	total between 11 and 30
Phone number 1	Market Area 1	1		Market Area 1	????	????
Phone number 2	Market Area 1	1
Phone number 3	Market Area 1	2
Phone number 4	Market Area 1	3
Phone number 5	Market Area 1	3
Phone number 6	Market Area 1	4
Phone number 7	Market Area 1	5
Phone number 8	Market Area 1	6
Phone number 9	Market Area 1	7
Phone number 10	Market Area 1	10
Phone number 11	Market Area 1	11
Phone number 12	Market Area 1	15
Phone number 13	Market Area 1	23
Phone number 14	Market Area 1	28
Phone number 15	Market Area 1	29

[#2 · Accepted solution]

@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.

[#3]

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)