Sumproduct or countifs when using left function over an array

[jblonde002]

Pardon my ignorance but I'm struggling with this one! I have an dataset as shown below:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]77+[/TD]
[TD]77[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]98-[/TD]
[TD]88[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]66=[/TD]
[TD]66[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]55+[/TD]
[TD]66[/TD]
[/TR]
</tbody>[/TABLE]

The formula I would like to crack requires comparing the leftmost digit from column A to the leftmost digit in column B over the array and summing the total that are under/on/over. So using this example:

Over target (where A>B): 1 A2
On target (where A=B): 2 A1,A3
Below target (where A<B) 1A<b: <strong="">1 A4

I've tried =countif(value(left(A1:A4,1)=value(left(B1:B4,1) but I'm definitely not on the right lines as Excel won't even let me enter it! Thanks for any help you can give!</b:>

 

[pgc01]

Hi

Try, for ex. for on target:

=SUMPRODUCT(--(LEFT(A1:A4)=LEFT(B1:B4)),B1:B4)

 

[jblonde002]

Thanks for replying. My example might be a bit unclear because when something is over/under it could be by any amount, but I would want it to count as "one example of being under" in this array.

For example comparing 66 to 99 I would not want it to return 3 or 33, but to return 1, because this is one comparison that is under.

 

[pgc01]

I thought you wanted to add column B for the cases that match.

If you just want to count them, try for on target:

=SUMPRODUCT(--(LEFT(A1:A4)=LEFT(B1:B4)))

 

[jblonde002]

Thanks so much pgc, another question bites the dust! I really appreciate your time

 

[pgc01]

You're welcome. Thanks for the feedback.