Sumproduct using part of a Cell
 

I have a worksheet in which I need to find all of the cells in one column
that end in specific criteria and count the number of times they meet other
criteria in other cells. Example:

Column A Column D Column R
5023 (R:50 D:505) 72 53.8%
5001 (R:50 D:500) 148 57.4%
5024 (R:50 D:500) 0 0.0%
5049 (R:50 D:507) 78 74.5%
5032 (R:50 D:507) 60 43.2%

I need to find how many stores in D:500 that don't have 0 in Column D are +
or - 10% of another cell that isn't listed here (which we will say is 55%),
and the same for D:507, etc. In this example, I should have 0 for D:505, 0
for D:500 and 1 that is 10% over and 1 that is 10% under for D:507.

This is a very small sampling of the actual sheet that I am using. I know
how to do the Sumproduct formula for this except for the fact that I need it
to look at the end of what is in Column A and only use the last 4 characters.

Thank you for your help in advance. Please let me know if need any other
information or a better explanation.

Paul Rozewski

Sumproduct using part of a Cell
 

The last 4 characters would be something like 505)

Maybe:

=sumproduct(--(right(a1:a10,6)="d:500)"),--(....),--(...))



prozewski wrote:

I have a worksheet in which I need to find all of the cells in one column
that end in specific criteria and count the number of times they meet other
criteria in other cells. Example:

Column A Column D Column R
5023 (R:50 D:505) 72 53.8%
5001 (R:50 D:500) 148 57.4%
5024 (R:50 D:500) 0 0.0%
5049 (R:50 D:507) 78 74.5%
5032 (R:50 D:507) 60 43.2%

I need to find how many stores in D:500 that don't have 0 in Column D are +
or - 10% of another cell that isn't listed here (which we will say is 55%),
and the same for D:507, etc. In this example, I should have 0 for D:505, 0
for D:500 and 1 that is 10% over and 1 that is 10% under for D:507.

This is a very small sampling of the actual sheet that I am using. I know
how to do the Sumproduct formula for this except for the fact that I need it
to look at the end of what is in Column A and only use the last 4 characters.

Thank you for your help in advance. Please let me know if need any other
information or a better explanation.

Paul Rozewski


--

Dave Peterson