Need EXCEL help with a SUM function

Vontech615
Vontech615 used Ask the Experts™
on
I have the attached spreadsheet in csv format to work with. Basically it's a list of locations  by Airport code (APK, DFW, UKR, etc) and the different model phones at those locations.   What I would like to do (not sure if it's possible) is take this list and get a count of each phone type (7940, 7941, 7962, etc) by location.  So, I would need it to somehow get a SUM based on location and model of phone.... I'm guessing I would need to insert or create a different function for each location?  I'm really a novice at EXCEL so I would greatly appreciate any help.
All_phone_types.csv
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Katherine HainesSenior Software Development Engineer
Commented:
Hopefully I'm following correctly, since your data isn't labeled. Is one column the location, and another the phone type?

If so, I would try doing it in two steps:

=CONCATENATE(text1, [text2], ...)
=COUNTIF (range, criteria)

Make a new column that is concatenating the two columns that are your location and type. For example it could look like this =CONCATENATE(A1, C1) and then you'd "fill down" for the rest of the column.

Now make another new column for occurrences. =COUNTIF($D$1:$D$5933, D1) and also "fill down" for the rest of the column.

For a lot more detail about countif, check this out.

Author

Commented:
Sorry, please see new file.... I've cleaned it up and added location and phone type.
Katherine HainesSenior Software Development Engineer

Commented:
I think perhaps you forgot to attach the file?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
You're right... sorry about that.
All_phone_types.csv
Katherine HainesSenior Software Development Engineer

Commented:
I'd still use the same technique I recommended. Do you have anymore questions?
Project Manager
Commented:
You can convert to excel and use pivot table.

Here as an example (added one collumn filled with "1" to count the phones you can use any other column you have like phone number or serial number  on the values of the pivot table
All_phone_types.xlsx

Author

Commented:
Wow, Lopo, that is exactly what I was looking to do and it looks like you've already included all the phones per location. That was quick. Can you point me to some good reading on how to work with Pivot tables?
Katherine HainesSenior Software Development Engineer

Commented:
I never think of pivot tables, I guess because they frustrate me.

But I would start here if I wanted to read more about them.

Author

Commented:
Thank you both for your help!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial