Help with formula involving multiple columns

Question

Hi&nbsp;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.&nbsp;Toll-Free NumberMarket AreaCompleted Calls&nbsp;&nbsp;Total between 0 and 10total between 11 and 30Phone number 1Market Area 11&nbsp;Market Area 1????????Phone number 2Market Area 11&nbsp;&nbsp;&nbsp;&nbsp;Phone number 3Market Area 12&nbsp;&nbsp;&nbsp;&nbsp;Phone number 4Market Area 13&nbsp;&nbsp;&nbsp;&nbsp;Phone number 5Market Area 13&nbsp;&nbsp;&nbsp;&nbsp;Phone number 6Market Area 14&nbsp;&nbsp;&nbsp;&nbsp;Phone number 7Market Area 15&nbsp;&nbsp;&nbsp;&nbsp;Phone number 8Market Area 16&nbsp;&nbsp;&nbsp;&nbsp;Phone number 9Market Area 17&nbsp;&nbsp;&nbsp;&nbsp;Phone number 10Market Area 110&nbsp;&nbsp;&nbsp;&nbsp;Phone number 11Market Area 111&nbsp;&nbsp;&nbsp;&nbsp;Phone number 12Market Area 115&nbsp;&nbsp;&nbsp;&nbsp;Phone number 13Market Area 123&nbsp;&nbsp;&nbsp;&nbsp;Phone number 14Market Area 128&nbsp;&nbsp;&nbsp;&nbsp;Phone number 15Market Area 129&nbsp;&nbsp;&nbsp;&nbsp;

mathetes · Accepted Answer

Rey721&nbsp;&nbsp;This formula in column F produces a count of 10 for instances between 0 and 10&nbsp;&nbsp;=COUNTA(FILTER(C2:C16,(C2:C16&gt;0)*(C2:C16&lt;=10)*(B2:B16="Market Area 1")))&nbsp;&nbsp;In column G, this one gave a count of 5, for instances between 11 and 30&nbsp;&nbsp;=COUNTA(FILTER(C2:C16,(C2:C16&gt;=11)*(C2:C16&lt;=30)*(B2:B16="Market Area 1")))&nbsp;&nbsp;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.

Forum Discussion

Help with formula involving multiple columns

Share

Resources

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