Using Right and Len formulas together

[PDGood]

This is a simplified version of my spreadsheet in order to explain what I'm trying to find:



Column A has the following info going from A1 to A12:
W-80085-B
W-79989-A
W-80031-A
W-80032-A
W-80033-A
W-80043-A
W-79935-B
W-79981-A
W-80114-A
W-79981-S
W-80114-S
W-80084-B
I'm looking for a formula to put in C1 that will tell me how many of these end in the letter A.

In a separate instance there will be various quantities in B1:B12. In that case I'm looking for another formula that will find each cell that ends in the letter A and then add all the quantities in the neighboring B cells. In other words, If cells 2,3,4,5,6, 8, and 9 in end A then I need the total of the info in B2,B3,B4,B5,B6,B8 and B9.

Thanks!

[Kevin@Radstock]

Hi PDGood

Try the following.

=SUMPRODUCT(--(RIGHT(A1:A12,1)="A"))

Sorry missed the second part.

=SUMPRODUCT(--(RIGHT(A1:A12,1)="A"),B1:B12)

[PDGood]

Excellent! Thanks! That is brilliant!!

[Kevin@Radstock]

I missed the second part but posted in the same reply.

[Debaser]

You can also use:
=COUNTIF(A1:A12,"*A")

and

=SUMIF(A1:A12,"*A",B1:B12)