SUMIF function on a lookup range

[patu123]

I am kind of new to this form but I am kind of stuck and need help desprately!

I am trying to sum total number of products sold by any perticular person in a lookup range and the formula I am using works if I specify acutal names but the problem is my lookup name range is about 200 names and am scared to physically type this names in a formula. Is there a better way to handle this.

The formula I am using is as:

=SUM(SUMIF(A5:A5450,{"JOHN","ROBERT","CHARLIE"},O5:O5450))

Any input will be a break for me!!!!

 

[Jonmo1]

Welcome to the board..

You can convert that to an array formula like

=SUM(SUMIF(A5:A5450,B1:B200,O5:O5450))

Where B1:B200 are your list of names to check (John, Robert, Charlie)

IMPORTANT
This is an array formula that requires CTRL + SHIFT + ENTER
After entering the formula, highlight cell with formula and press F2
Then press CTRL + SHIFT + ENTER
When entered correctly, the formula will be enclosed in {brackets}

Hope that helps.

 

[jeffreybrown]

Or I was going to suggest using a named range. names is the named range which encompasses the list of names.

and then =SUMPRODUCT(SUMIF(A5:A5450,names,O5:O5450))

 

[T. Valko]

I am kind of new to this form but I am kind of stuck and need help desprately!

I am trying to sum total number of products sold by any perticular person in a lookup range and the formula I am using works if I specify acutal names but the problem is my lookup name range is about 200 names and am scared to physically type this names in a formula. Is there a better way to handle this.

The formula I am using is as:

=SUM(SUMIF(A5:A5450,{"JOHN","ROBERT","CHARLIE"},O5:O5450))

Any input will be a break for me!!!!

List the names in a range of cells:

• X5 = JOHN
• X6 = ROBERT
• X7 = CHARLIE

Then the formula is:

=SUMPRODUCT(--(ISNUMBER(MATCH(A5:A5450,X5:X7,0))),O5:O5450)

 

[patu123]

Awesome guys, you are the best this worked. I tried this earlier but wasnt working but I want making it absoulute (ctrl + shift + enter) but all is good now!!!

Made my day and now I can start cuising back!!!!