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