Hello everyone,
I have a file with multiple sheets, each sheet has a list of IDs and next to it a number.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Number[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]345[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]334[/TD]
[/TR]
[TR]
[TD]021[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]145[/TD]
[TD]3453[/TD]
[/TR]
</tbody>[/TABLE]
All the sheets have the above format.
I collect and sum all the numbers for each id from all the sheets with this formula
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
I have a file with multiple sheets, each sheet has a list of IDs and next to it a number.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Number[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]345[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]334[/TD]
[/TR]
[TR]
[TD]021[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]145[/TD]
[TD]3453[/TD]
[/TR]
</tbody>[/TABLE]
All the sheets have the above format.
I collect and sum all the numbers for each id from all the sheets with this formula
Code:
=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
Last edited: