Summing 2 ifs

[Steve]

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

 

[PCLIVE]

Try this:

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

 

[Pete_UK]

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

 

[Steve]

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)

 

[PCLIVE]

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]

I still can't get it to work, so I'll understand if you're becoming
impatient, and have other things to do. But just in case, here is the actual
data and refs, to better understand:

S3 and down has this formula =VLOOKUP(E3,Data!$B$2:$G$50,6,FALSE)
U3 and down has this formula =LEFT(D3,1)

I need to add up all the U1s with S column Ms in S so I get 30.14
The U2s with S column Ms so I get 36.05 and
U3s with S column Ms so I get 26.5.
The #N/As are not needed.
Again, much appreciated if it can be fixed.

D E F N S
U
112 4444 Montana 8 M 1
112 5555 Farve 8 M 1
122 6666 Aiken 8 M 1
198 7777 Manning 6.14 M 1
212 9999 Young 8 M 2
212 8888 Unitas 8 #N/A 2
212 9991 Starr 10 M 2
212 8884 Elway 10.05 M 2
222 6664 Rice 8 M 2
312 2223 Payton 8 M 3
312 1245 Brown 10.5 M 3
312 1478 Sayers 8 M 3
312 4587 Morino 8 #N/A 3
312 5689 taylor 8 #N/A 3

 

[PCLIVE]

Steve,

If you want to send me the spreadsheet I can take a look at it.

--

 

[PCLIVE]

You might try changing your formula to:

=LEFT(D3,1)*1

Then leave off the quotes around the numbers as previously recommended.

--