How can I SUMIFS with leading zeros

[Dark0Prince]

=SUMIFS('GJS2'!$C:$C,'GJS2'!$A:$A,GJNETS!$A8)

I need to be able to add 0999 client separate from 999 client and it's adding them together.
Iv'e for mated it as text and tried putting the whole code in a SUMPRODUCT.

 

[RoryA]

You have to use SUMPRODUCT instead of SUMIF:

=SUMPRODUCT('GJS2'!$C$1:$C$1000,--('GJS2'!$A$1:$A$1000=GJNETS!$A8))

adjusting the ranges as necessary. (Don't use entire column references)

 

[Dark0Prince]

You have to use SUMPRODUCT instead of SUMIF:

=SUMPRODUCT('GJS2'!$C$1:$C$1000,--('GJS2'!$A$1:$A$1000=GJNETS!$A8))

adjusting the ranges as necessary. (Don't use entire column references)

That only sums the first one it finds not if it finds it multiple times.

 

[RoryA]

No, it doesn't. If you're only getting the total for one, then either you haven't adjusted the ranges to match your data, or the other data is not actually a match - e.g. it's just formatted to show a leading 0.

 

[Dark0Prince]

No, it doesn't. If you're only getting the total for one, then either you haven't adjusted the ranges to match your data, or the other data is not actually a match - e.g. it's just formatted to show a leading 0.

You were right! thanks, I fixed the range.

 

[RoryA]

Glad to help.