MATCH or VLOOKUP or ??

[SMAX]

Sorry about that. I am new to this and didn't think of the problem
associated with an attachment.

I am really unsure as how to explain what is going on or not going on.
I used the following formula:

=SUMIF(Calls!$A$2:$A$176,A2,Calls!$B$2:$B$176)

Here is the setup:

Sheet One: called "Master List" - This sheet as all of the possibl
types of calls (located in column A with a header and then data fro
A2:A165).

In column B2, I used the fomula above.

Sheet Two: called "Calls" - I have a list of all of the call type
(Header in column A1 & data starts in A2 and goes to A176). In th
next column (B), I have all of the data for each call (header in B1
data starts in B2 and continues to B176)

The odd thing is that the formula works fine until it gets down to hal
way and then does not return the correct data.

I have tried various changes, but still cannot get it to work. I hav
also included small image files showing my work and the formulas alon
with the problem areas.

Again, any help is really (really) appreciated.

Thanks again,

Gu

+-------------------------------------------------------------------
|Filename: Sumif Pics.zip
|Download: http://www.excelforum.com/attachment.php?postid=2685
+-------------------------------------------------------------------

 

[Max]

=SUMIF(Calls!$A$2:$A$176,A2,Calls!$B$2:$B$176)

The odd thing is that the formula works fine until it gets down to half
way and then does not return the correct data.

I think probably some data in either "Calls!$A$2:$A$176"
and/or in col A in sheet:Master List may contain "invisible"
stray leading or trailing spaces throwing the matching off
(especially with the alphanumeric call types e.g. 1070A, etc)

Assuming the SUMIF above is in B2,
try this equivalent formula in an adjacent empty cell, say in cell C2
(TRIM() added to help clean up the stray spaces)

=SUMPRODUCT((TRIM(Calls!$A$2:$A$6)=TRIM(A2))*1,Calls!$B$2:$B$6)

Copy C2 down

Compare the results returned in cols B and C

 

[Max]

Oops, sorry, forgot to change the ranges to match the SUMIF ..

The corrected formula to try in C2:

=SUMPRODUCT((TRIM(Calls!$A$2:$A$176)=TRIM(A2))*1,Calls!$B$2:$B$176)

 

[duane]

=if(isna(match(call,actual call list,0)),0,vlookup(call,actual cal
list,2,false))

this worked fine for me - note I changed the match type 0 and adde
false to vlookup.....also, the actual call list in the vlookup o
course contains two column