Home |
Search |
Today's Posts |
#1
|
|||
|
|||
I've tried DSUM, SUMIF....
Can anyone please help me solve this frustrating problem!
I have 5 columns of info about my phone calls (the standard stuff: date, start time, no called, duration, cost). Column E is the 'amount' column, and I want to total all the amounts for calls which (in column 3) START with a 7. In other words, I'm trying to find out how much was spent on all calls to mobile phones (all leading zeros are removed). Its easy to find totals for calls to particular numbers, but I can't figure out how to total them all. The only criteria I can think of is that they do all begin with a 7. I've tried using 7* in both sumif and dsum scenarios but it doesn't work. How can it be done? Thanks Jeff |
#2
|
|||
|
|||
=SUMPRODUCT(--(LEFT(C2:C200)="7"),E2:E200)
-- HTH Bob Phillips "Jeff" wrote in message ... Can anyone please help me solve this frustrating problem! I have 5 columns of info about my phone calls (the standard stuff: date, start time, no called, duration, cost). Column E is the 'amount' column, and I want to total all the amounts for calls which (in column 3) START with a 7. In other words, I'm trying to find out how much was spent on all calls to mobile phones (all leading zeros are removed). Its easy to find totals for calls to particular numbers, but I can't figure out how to total them all. The only criteria I can think of is that they do all begin with a 7. I've tried using 7* in both sumif and dsum scenarios but it doesn't work. How can it be done? Thanks Jeff |
#3
|
|||
|
|||
Thank you thank you thank you Bob
"Bob Phillips" wrote in message ... =SUMPRODUCT(--(LEFT(C2:C200)="7"),E2:E200) -- HTH Bob Phillips "Jeff" wrote in message ... Can anyone please help me solve this frustrating problem! I have 5 columns of info about my phone calls (the standard stuff: date, start time, no called, duration, cost). Column E is the 'amount' column, and I want to total all the amounts for calls which (in column 3) START with a 7. In other words, I'm trying to find out how much was spent on all calls to mobile phones (all leading zeros are removed). Its easy to find totals for calls to particular numbers, but I can't figure out how to total them all. The only criteria I can think of is that they do all begin with a 7. I've tried using 7* in both sumif and dsum scenarios but it doesn't work. How can it be done? Thanks Jeff |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use SUMIF to return sums between two values located in cells | Excel Worksheet Functions | |||
Embedding a Sumif in a sumif | Excel Worksheet Functions | |||
SUMIF with Mutiple Ranges & Criteria | Excel Discussion (Misc queries) | |||
DSUM vs SUMIF | Excel Worksheet Functions | |||
formulas SUMIF & DSUM | Excel Discussion (Misc queries) |