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.

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

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
>
>

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.

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 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
>
>

Kim,
this formula should do your job.

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

HTH
Kostis Vezerides