Exact match in SUMIF criteria

[kamperone]

Hello everyone,

I have a file with multiple sheets, each sheet has a list of IDs and next to it a number.

ID	Number
1	345
21	334
021	23
145	3453

<tbody>
</tbody>

All the sheets have the above format.

I collect and sum all the numbers for each id from all the sheets with this formula

=SUMPRODUCT(SUMIF(INDIRECT("'"&$J$9:$J$20&"'!C7:C203");C7;INDIRECT("'"&$J$9:$J$20&"'!D7:D203")))

My problem comes at the criteria. I have an ID eg 21 and another ID 021 which are considered different. However, my formula treats them the same and and creates a sum of both 21 and 021. Is there a way to treat the cells differently and not as the same? I have already set them as text, but it's still not working.

I'm using Excel 2013 and Windows 7

 

[Drrellik]

I don't think you will be able to use a formula to do this, you are right excel treats 021 and 21 as the same even if you format it to text to get the 0 to show, you might consider changing the 021 to .021 if that is not to much trouble and then excel will work correctly.

you can use find and replace to change all the 021 to .021 without much trouble.

sorry I could not be more help.

~DR

 

[kamperone]

Thank you Drrellik. This worked indeed. I wish there was another way, but for the time that will do.

Thanks again!

 

[Drrellik]

your welcome thank you for the feedback.

 

[chrissyrw]

I'm not sure what method you used to format the cell as text. However, to get the cell to display the leading zero and to have Excel truly treat it as a text string, enter the data in the ID field as:

="021"

In my experience, Excel will then recognize that 021 is different than 21.

Let me know if you need me to clarify further. Hope that helps!