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!
Need EXCEL help with a SUM function
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
All_phone_types.csv
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
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.
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
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
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?
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.
But I would start here if I wanted to read more about them.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial