Help with creating a formula

[KB]

Hello!
I am trying to create a formula that will sum up certain criteria from one
column as it relates to criteria in the same row in another column. For
example, I want to sum up all the entries that begin with the letter "H" in
column A as it relates to an entry of "2" from column B that is in the same
row as the "H" entry from column A.

COLUMN A COLUMN B
H10 2
G01 2
H09 3
H15 2
H12 2
If there is such a formula, the answer from the example would be 3

Any help would be appreciated! Thanks!

 

[Mike H]

Hi,

Try this

=SUMPRODUCT((LEFT(A1:A5,1)="h")*(B1:B5=2))
Mike

 

[KB]

Hello and thank you for your reply...much appreciated, but for some reason,
the formula didn't calculate the correct figure... I think the formula is
multiplying the sum of criteria in each column together vs. looking at there
relationship to one another and summing up the entries for one column. (My
apologies if I didn't articulate my problem well) I actually needed to sum
up entries in column A that had an "H", but only if the entry in column B had
a "2" in the cell.

Thanks again for your help Mike!

 

[T. Valko]

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?

 

[KB]

I tried the formula again, and it worked! I cut & pasted the formula this
time vs. I hand typed it the last time...that's probably why it didn't
worked. And sorry about using the term sum when I really meant count, but
thank you for understanding what I needed. You have been a great help! Thanks
Mike & Biff!

P.S. To change the formula a bit...if column B included text and the number
2, would the last part of the formula be:
(B1:B5,1)="2")* Just wanted to know, in case the identifying codes are
changed in the future to include an alpha/text. Thanks!

-KB

 

[Jacob Skaria]

If you mean data like the below...try the below formula...Please note that
functions LEFT() and RIGHT() are used to pick the left and right most
character. Refer help on those..you can even refer the number of characters
to be picked up..from left or right. Also when you specify text you will have
to specify that within double quotes..Since RIGHT and LEFT functions return
text strings we have used "2" instead of 2

H10 B2
G01 A2
H09 A3
H15 C2
H12 A2

=SUMPRODUCT((LEFT(A1:A5)="h")*(RIGHT(B1:B5)="2"))

 

[KB]

Thank you so much! I appreciate the information

-KB