Looks like C is being treated as text.
Try this: (basically, put quotes around the numbers for column C.

1's
=SUMPRODUCT(--(B1:B100="M"),--(C1:C100="1"),A1:A100)

2's
=SUMPRODUCT(--(B1:B100="M"),--(C1:C100="2"),A1:A100)

3's
=SUMPRODUCT(--(B1:B100="M"),--(C1:C100="3"),A1:A100)

HTH,
Paul

"Steve" wrote in message
...
Thanks guys, but I'm not able to get either suggestion to work. I think it
may be because Columns B & C are formula references as follows:

B = VLOOKUP(E3,'data'!$B$2:$G$50,6,FALSE) and
C is =LEFT(D3,1)

"Pete_UK" wrote:

In column D you can record these values:

D1: 1
D2: 2
D3: 3

Then put this formula in E1:

=SUMPRODUCT((C$1:C$20=D1)*(B$1:B$20="M")*(A$1:A$20 ))

and copy it down into E2:E3. Adjust ranges if you have more than 20
rows of data.

Hope this helps.

Pete

On Mar 11, 8:13 pm, Steve wrote:
I need to add the A column if B has M and C has 1, and also for the 2's
in C
if M & the 3's in C if M.
I need 24 for my corresponding 1s
36.05 for my corresponding 2s
42.5 for my corresponding 3s
The #N/A s are not needed.
I don't think I asked this very well, but I hope it's understandable.
Thanks,

A B C
8 M 1
8 M 1
8 M 1
6.14 #N/A 1
8 M 2
8 M 2
10 M 2
10.05 M 2
8 #N/A 2
8 M 3
10.5 M 3
8 M 3
8 M 3
8 M 3