Formula only working sometimes

[Guest]

I have this formula to calculate the weight of material typeson a seperate
worksheet.
=SUMIF('ROCKY POINT REVENUE'!J9:J1237,A11,'ROCKY POINT REVENUE'!I9:I1237)
in column J is my material type and column I is my weight of material. I
have about 20 different material types and may have over 1000 differnet
entries for the month. This formula works for most materials but for some
reason will not detect names of other material. The material types that are
not being detected are 1 word written all in capital lettering much the same
as other aterial types that are being detected. Any assistance would be
great.

Keiran

 

[Guest]

Hello Kieran,

Your formula looks OK to me, it's not case-sensitive so it won't matter if
your data is in upper or lower case but perhaps you have some extra spaces in
the data. Get rid of those or try

=SUMPRODUCT(--(TRIM('ROCKY POINT REVENUE'!J9:J1237)=A11),'ROCKY POINT
REVENUE'!I9:I1237)

 

[Guest]

Thanks

for the suggestion, much appreciated.

Hello Kieran,

Your formula looks OK to me, it's not case-sensitive so it won't matter if
your data is in upper or lower case but perhaps you have some extra spaces in
the data. Get rid of those or try

=SUMPRODUCT(--(TRIM('ROCKY POINT REVENUE'!J9:J1237)=A11),'ROCKY POINT
REVENUE'!I9:I1237)