ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Summing 2 ifs (https://www.excelbanter.com/excel-worksheet-functions/179682-summing-2-ifs.html)

Steve
Summing 2 ifs
 
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
Summing 2 ifs
 
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

--

"Steve" wrote in message
...
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

Pete_UK
Summing 2 ifs
 
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

Steve
Summing 2 ifs
 
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

PCLIVE
Summing 2 ifs
 
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

Steve
Summing 2 ifs
 
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" wrote:

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

PCLIVE
Summing 2 ifs
 
Steve,

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

--

"Steve" wrote in message
...
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" wrote:

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

PCLIVE
Summing 2 ifs
 
You might try changing your formula to:

=LEFT(D3,1)*1

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

--

"PCLIVE" wrote in message
...
Steve,

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

--

"Steve" wrote in message
...
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" wrote:

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


All times are GMT +1. The time now is 04:20 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com