Problem with SUMIF criteria
 

Hi Everyone,

Please excuse the "I'm a glue-sniffer" question as this is my first
post, but could anyone tell me how to attach an xls file to a post.

I'm having a problem with a SUMIF criteria and thought my query would
make more sense if people could actually see what I'm trying to achieve
along with my explanation in the post.

Any help would be much appreciated.

Kind regards,

Kim.


--
Kimhull
------------------------------------------------------------------------
Kimhull's Profile: http://www.excelforum.com/member.php...o&userid=31010
View this thread: http://www.excelforum.com/showthread...hreadid=506847

Problem with SUMIF criteria
 

Kim,
post the formula you are using. Indicate in what way it is not working:
Error values? Wrong values? Zero?

Are you trying to sum with more than one criteria?

Kostis Vezerides

Problem with SUMIF criteria
 

Attaching files to posts in these Newsgroups is generally frowned upon.
Please try just explaining your problem, no doubt someone will be able to
help. If we feel we need more info, we'll ask for it, and/or someone may
volunteer for you to send a file directly to their email addy........

Vaya con Dios,
Chuck, CABGx3



"Kimhull" wrote:


Hi Everyone,

Please excuse the "I'm a glue-sniffer" question as this is my first
post, but could anyone tell me how to attach an xls file to a post.

I'm having a problem with a SUMIF criteria and thought my query would
make more sense if people could actually see what I'm trying to achieve
along with my explanation in the post.

Any help would be much appreciated.

Kind regards,

Kim.


--
Kimhull
------------------------------------------------------------------------
Kimhull's Profile: http://www.excelforum.com/member.php...o&userid=31010
View this thread: http://www.excelforum.com/showthread...hreadid=506847

Problem with SUMIF criteria
 

Thanks for your replies,

The formula I'm attempting to use is:

=SUMIF($A$3:$A$2001,M3:M100,$G$3:$G$2001)

I'm trying to get it to calculate the duration in minutes of calls that
a group of moblie phones make to each other, this will then eventually
be expressed as a percent of the total duration of all calls that group
made.

In the worksheet I'm using Column A is an imported list of all numbers
dialled by one specific handset from the group. Column G is the
duration in minutes of each number dialled. Column M contains a list of
all the existing phone numbers within the group.

From my understanding the range A3:A2000 should be evaluated by the
criteria M3:M100 (comparing the existing phone numbers to the numbers
dialled) and then the duration in minutes for those cells defined by
the criteria should be summed returning the total number of minutes
that the specific handset used in calling other group members.

Unfortunately from my test it only returns a value of 0 unless I modify
the criteria to reflect only 1 cell from the range M3:M100. It then
works fine but only calculates the calls made to the number in that
cell.

I know I can get around this with something along these lines:

=SUMIF($A$3:$A$2001,M3,$G$3:$G$2001)+
SUMIF($A$3:$A$2001,M4,$G$3:$G$2001)+
SUMIF($A$3:$A$2001,M5,$G$3:$G$2001) and so on and so on…

The problem with this is when I have to analyse a large number of
handsets, 100+ etc, I will spend more time entering the formula than
doing the actual analysis.

As I’m also at the “monkey with a keyboard” stage of learning VBA,
which I’m sure would be able to perform the required task easily, I’m a
bit stumped… Especially as the formula doesn’t return any error.

Any help you could give me in pointing me in the right direction to see
where I have gone wrong would be greatly appreciated.

Many thanks.


--
Kimhull
------------------------------------------------------------------------
Kimhull's Profile: http://www.excelforum.com/member.php...o&userid=31010
View this thread: http://www.excelforum.com/showthread...hreadid=506847

Problem with SUMIF criteria
 

Maybe with a helper column, say column B, enter this in B3 and copy down......

=IF(ISNA(VLOOKUP(A3,$M$3:$M$100,1,FALSE)),"",1)

Then just do a =COUNT(B:B) and you will get a count of all the numbers that
are in column A and also in column M

hth
Vaya con Dios,
Chuck, CABGx3






"Kimhull" wrote:


Thanks for your replies,

The formula I'm attempting to use is:

=SUMIF($A$3:$A$2001,M3:M100,$G$3:$G$2001)

I'm trying to get it to calculate the duration in minutes of calls that
a group of moblie phones make to each other, this will then eventually
be expressed as a percent of the total duration of all calls that group
made.

In the worksheet I'm using Column A is an imported list of all numbers
dialled by one specific handset from the group. Column G is the
duration in minutes of each number dialled. Column M contains a list of
all the existing phone numbers within the group.

From my understanding the range A3:A2000 should be evaluated by the
criteria M3:M100 (comparing the existing phone numbers to the numbers
dialled) and then the duration in minutes for those cells defined by
the criteria should be summed returning the total number of minutes
that the specific handset used in calling other group members.

Unfortunately from my test it only returns a value of 0 unless I modify
the criteria to reflect only 1 cell from the range M3:M100. It then
works fine but only calculates the calls made to the number in that
cell.

I know I can get around this with something along these lines:

=SUMIF($A$3:$A$2001,M3,$G$3:$G$2001)+
SUMIF($A$3:$A$2001,M4,$G$3:$G$2001)+
SUMIF($A$3:$A$2001,M5,$G$3:$G$2001) and so on and so on€¦

The problem with this is when I have to analyse a large number of
handsets, 100+ etc, I will spend more time entering the formula than
doing the actual analysis.

As Im also at the €œmonkey with a keyboard€� stage of learning VBA,
which Im sure would be able to perform the required task easily, Im a
bit stumped€¦ Especially as the formula doesnt return any error.

Any help you could give me in pointing me in the right direction to see
where I have gone wrong would be greatly appreciated.

Many thanks.


--
Kimhull
------------------------------------------------------------------------
Kimhull's Profile: http://www.excelforum.com/member.php...o&userid=31010
View this thread: http://www.excelforum.com/showthread...hreadid=506847

Problem with SUMIF criteria
 

Kim,
this formula should do your job.

SUMPRODUCT($G$3:$G$2001*--(COUNTIF(M3:M100,$A$3:$A$2001)<0))

HTH
Kostis Vezerides