SUMIF WITH AND OR ANOTHER SUGGESTION

[Greg]

Looking to sumif A1 and B1 = "MOTOROLA JXP1" then C1 would be the anwser

A B C
1 MOTOROLA JXP 1 4
2 MOTOROLA JXP 2 5
3 MOTOROLA JXP 3 6
4 MOTOROLA JXP 4 8


Thanks, Greg

 

[Barb Reinhardt]

It's not clear to me how you get 4, 5, 6 or 8 for column C. Please advise
and I can give more suggestions.

Barb Reinhardt

 

[Greg]

Each cell would have the same formula, except it looks for 2, then 3, then 4

 

[Greg]

Also this data is on another sheet. The master summary sheet has the formulas

 

[Barb Reinhardt]

Specifically, how do you determine that the value is 4 for the first row, 5
for the second?

Barb Reinhardt

 

[Greg]

This is a part that is part of a BOM. The data or design will tell me how
many of "JXP*"s [*=value] that I need. Unfortunitely, the program places
part of the name in "A" and the other half in "B". I need to match "A" &
"B", when this is done, the anwser to the formula is "C".

 

[jayray]

This is a part that is part of a BOM.  The data or design will tell me how
many of "JXP*"s [*=value] that I need.  Unfortunitely, the program places
part of the name in "A" and the other half in "B".  I need to match "A" &
"B", when this is done, the anwser to the formula is "C".

Specifically, how do you determine that the value is 4 for the first row, 5
for the second?

Barb Reinhardt

"Greg" wrote:

- Show quoted text -

SUMIF works only if the range to search (A1:A4) and the criterion
("Motorola") are numbers--hence the SUM part of SUMIF--not text.

 

[Greg]

"c" CAN BE ANY NUMBER, BUT THE ANWSER HAS TO BE THE VALUE OF "c"

This is a part that is part of a BOM. The data or design will tell me how
many of "JXP*"s [*=value] that I need. Unfortunitely, the program places
part of the name in "A" and the other half in "B". I need to match "A" &
"B", when this is done, the anwser to the formula is "C".

Specifically, how do you determine that the value is 4 for the first row, 5
for the second?

Barb Reinhardt

 

[Greg]

What do you recommend? I need to find the row that the combined "A"&"B" = x,
then the anwser is "C" on the same row.

This is a part that is part of a BOM. The data or design will tell me how
many of "JXP*"s [*=value] that I need. Unfortunitely, the program places
part of the name in "A" and the other half in "B". I need to match "A" &
"B", when this is done, the anwser to the formula is "C".

Specifically, how do you determine that the value is 4 for the first row, 5
for the second?

Barb Reinhardt

"Greg" wrote:

Each cell would have the same formula, except it looks for 2, then 3, then 4

"Barb Reinhardt" wrote:

It's not clear to me how you get 4, 5, 6 or 8 for column C. Please advise
and I can give more suggestions.

Barb Reinhardt

"Greg" wrote:

Looking to sumif A1 and B1 = "MOTOROLA JXP1" then C1 would be the anwser

A B C
1 MOTOROLA JXP 1 4
2 MOTOROLA JXP 2 5
3 MOTOROLA JXP 3 6
4 MOTOROLA JXP 4 8

Thanks, Greg- Hide quoted text -

- Show quoted text -

SUMIF works only if the range to search (A1:A4) and the criterion
("Motorola") are numbers--hence the SUM part of SUMIF--not text.

 

[Ron Coderre]

With your sample data in A1:C4

Try this:
E1: (the combo to find.....eg "MOTOROLA JXP 1")

This formula returns the sum of Col_C values associated with that combo:
E2: =SUMPRODUCT(--(A1:A4&" "&B1:B4=E1),C1:C4)

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Greg]

It worked out perfect, I forgot the sumproduct function...thanks

 

[Ron Coderre]

I'm glad that worked for you.

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)