Sum If - If contains Please help!

[amwilber]

Hello, I have a slightly tricky one someone here will easily be able to help with.

I would like to do a summation on the data below, but only when the information equals a Main/Sub equaling "S" and also a Item No containing "10".

So in the example below the sum would end up being "$1,745.44".

Please help, thanks ahead of time!

Item No	Main/Sub	Target Value
100	M	2,327.25
110	S	1,745.44
120	S	581.81

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[Joe4]

Assuming your data is in range A2:C4, this formula will do what you want:

=SUMPRODUCT(((B2:B4)="S")*(RIGHT(A2:A4,2)="10")*(C2:C4))