Multiple value lookups from a table to select & sum data from colu

[brettster999]

I have a task to analyze GPRS usage on a company's monthly cellular bill for
each individual phone. There are many telephones in the fleet and the rawdata
bill contains line items for all phones and different call types (GPRS, GPRS
Roaming, CSD, etc ). I am trying to extract and summarize total usage of
each call type for each individual tel number automatically (the report
varies in length each month):

My data set is in the following format

Tel Nr. Call Type Data (KB) used
012345678 GPRS 98762
033465756 GPRS 78923
012345678 GPRS ROAM 78902
056434231 GPRS ROAM 12323
012345678 GPRS 89564

I would like to be able to create a formula in a cell in my report that
allows me to look down the line items, pick out and sum total the data used
values that meet the criteria I set for the data in this case Tel Nr=
012345678 and Call type is GPRS giving an answer of 188326 KB GPRS used for
012345678. Thanks in advance!

 

[JLatham]

Excel 2007 has a SUMIFS() function that could do that. But if you're using
2003 or earlier (and even 2007), this will work.

I'm assuming that your data is in columns A, B and C starting at row 1 and
going on down to row 6.

For this example we'll use columns E, F and G for the solution.
E F G
1 Phone Type Total Data Used (KB)
2 012345678 GPRS =SUMPRODUCT(--(A2:A6=E2),--(B2:B6=F2),C2:C6)

you enter the information in E2 and F2 and G2 shows the results. Change the
references to the ranges like A2:A6 to refer to the correct ranges, for
example, if your data went down to row 1043, then the references would be
A2:A1043, B2:B1043 and C2:C1043.

Although a more generic formula, requiring less maintenance would be:
=SUMPRODUCT(--(A2:A65535=E2),--(B2:B65535=F2),C2:C65535)

 

[brettster999]

Hi JLatham
Thanks for responding. I tried your suggestion but it just returns a 0 for
me. Have looked at the cell formats and can't see anything unusual. Any
ideas why I can't get the reponse back I am looking for?

Brettster999

 

[JLatham]

Could be a format of data problem, most likely with the telephone numbers.
Since your column A is displaying a leading zero, I presume those are either
formatted as Text or have some special number formatting.

Here's one way to get the E2 cell formatted like the others. Chose one of
the numbers in column A and use Edit | Copy and then click in E2 and use
Edit | Paste Special with either the "All" or "Format" option selected.

If your column A is formatted as text (and E2 is also formatted as Text)
then you have to watch for leading/trailing blanks in both the entries in
column A and in E2. When comparing text entries, "01234" is not the same as
"01234 " or " 01234".

Make sure you have the A2:A#, B2:B# and C2:C# references all indicating the
same ending row. I suspect that they are, as if they weren't you'd get an
error indication rather than the 0 - but just checking.

If none of this seems to help and no one else offers any more ideas, you
could send the workbook to me as an attachment to an email and I'll try to
get it going. Remove all spaces from this address:
Help From @ JLatham site.com

 

[Max]

Maybe there's some extraneous white spaces in either col B/F's data throwing
off the matching, and maybe col C may be containing text numbers or a mix of
text/real numbers ..

Try this slight amendment to Jerry' suggestion which takes care of the above
2 possibilities:
=SUMPRODUCT(--(A2:A6=E2),--(TRIM(B2:B6)=TRIM(F2)),C2:C6+0)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 15,500, Files: 352, Subscribers: 53
xdemechanik

 

[brettster999]

Hi there Max - Yes that got it sorted - Thanks very much for your help!

 

[Max]

Welcome. Could you take a moment to press the "Yes" button below? Do the same
for Jerry's earlier responses which also helped
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 15,500, Files: 352, Subscribers: 53
xdemechanik