how to incorporate OR into a SUMIF formula

I don't know if I need to upload an example... this is pretty vanilla.

Column M= profit
Column F= salesman 1
Column G= salesman 2
Column E= N for new or U for used
Cell AA4 + salesman

I want to pull the sum of profit in column M if AA4 matches EITHER column F OR column G AND N in Column E.

I could do this using 3 columns with straight up sumifs for each salesman position, then totaling... but I have multiple columns to do this for, and I'm wondering how I might incorporate the OR function within a regular SUMIFS string.

here's the formula I need to improve:

Please Login or Register  to view this content.

That's not easy to do with SUMIF try SUMPRODUCT like this

=SUMPRODUCT(DEALS!M9:M408*(DEALS!E9:E408="N")*(DEALS!F9:G408=AA4))

Can col F and G in the same row contain the same Salesman name? If so it will count that row twice.....

no, they will never have the same name in F or G.

that would just be a single salesman deal.

will sumproduct only work if the columns are adjacent?

Originally Posted by lumberjim

will sumproduct only work if the columns are adjacent?

If they are not adjacent you can use this version

=SUMPRODUCT(DEALS!M9:M408,(DEALS!E9:E408="N")*((DEALS!F9:F408=AA4)+(DEALS!G9:G408=AA4)>0))

Thanks! Working!

NEW DEAL LOG 4-23-13.xlsx

is there a variation of countifs that will work this way?

I now need to count units based on multiple conditions for salesman if they are in either column.

ex: on the STAFF sheet, I figured a way of getting how many cash deals a salesman has, but it took me 5 columns to come up with it.

I need to look at the deals sheet, and count how many of X for a salesman, whether he is first or second... the wrinkle comes because when there ARE 2 salesmen, I need to give them half of the unit... so I have to isolate how many split deals they had that are X also... and then subtract it form the raw count.

thx
j

too ambiguous?

lemme sup up

I have columns on the DEALS sheet where we enter the deal data/.... such as date, customer, stock #, etc.

When there are 2 salesman, the deal gets split, and each salesman gets half of the gross or unit for that deal. I want to keep the data entry super basic and idiot proof... so I want the sheet to do all of the work. I need a formula that can look at a range of rows on the DEAL sheet, and give me data based on a match in EITHER column F or G... but sometimes with multiple criteria.

So if I need to count how many New cars James sold that were Leased, but I want a 1 when he's the only salesman (his name would appear in column F, and G would be empty) or 0.5 if there is another salesman in the row. ( he could be in column F OR G). There is a column that indicates New or Used with an N or a U; also a column that indicates (C)ash (F)inanced or (L)eased vehicles.

I want the result to total all cars(rows) that match his name (Staff!AA6) and then live on the STAFF sheet (AY6). See the above post's attachment for how I solved it.. I just think what I'm doing can't be the easiest way.

can you see a simpler way of counting given the split deal issue?