Mike's formula does what you wanted and does return the correct result based
on your posted sample data.
Your using the term SUM when what you really want is a COUNT.
You want to COUNT the number of instances where 2 conditions are met.
=SUMPRODUCT((LEFT(A1:A5,1)="h")*(B1:B5=2))
Result = 3
Here's how it gets that result...
Each of these expressions will return an array of either TRUE or FALSE:
(LEFT(A1:A5,1)="h")
(B1:B5=2)
LEFT(A1:A5,1)="h" tests the 1st character in each cell in the range A1:A5 to
see if it is the letter H (case doesn't matter). If it is then the result is
TRUE. If it isn't then the result is FALSE.
H10 = LEFT("H10",1)="h" = TRUE
G01 = LEFT("G01",1)="h" = FALSE
H09 = LEFT("H09",1)="h" = TRUE
H15 = LEFT("H15",1)="h" = TRUE
H12 = LEFT("H12",1)="h" = TRUE
B1:B5=2 tests that each cell in the range B1:B5 contains the *numeric* value
2. If it does then the result is TRUE. If it doesn't then the result is
FALSE.
2 = 2=2 = TRUE
2 = 2=2 = TRUE
3 = 3=2 = FALSE
2 = 2=2 = TRUE
2 = 2=2 = TRUE
These 2 arrays are then multiplied together and the result will be an array
of 1s and 0s. TRUE * TRUE = 1, any other combination = 0.
(LEFT(A1:A5,1)="h")*(B1:B5=2)
TRUE * TRUE = 1
FALSE * TRUE = 0
TRUE * FALSE = 0
TRUE * TRUE = 1
TRUE * TRUE = 1
This array of 1s and 0s is then summed to arrive at the final result:
=SUMPRODUCT({1;0;0;1;1})
Result = 3
You said the result you get is incorrect but you didn't say what that result
was. So, this tells me that your data may not be what it appears to be. You
might have unseen whitespace characters in either or both of the ranges.
Like this:
<space>H10
2<space>
So:
LEFT("<space>H10",1)="h" = FALSE
2<space> = 2 = FALSE
Or, maybe your numbers are TEXT numbers and not numeric numbers (there is a
difference!):
"2" = 2 = FALSE
So, you have to figure out what the problem is!
Try breaking the formula down to individual tests:
=SUMPRODUCT(--(LEFT(A1:A5,1)="h"))
What result do you get with that formula?
=SUMPRODUCT(--(B1:B5=2))
What result do you get with that formula?