COUNTIFS AND OR or some other magic trick that will solve this issue...

[lumberjim]

in the attached spreadsheet, 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... AND match one or two other columns as well.
So if I need to count how many (N)ew cars James sold that were (L)eased, 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) entered on the DEALS sheet that match his name from (Staff!AA6) and then live on the STAFF sheet (AY6). I have figured a way of getting what I want (see STAFF!AY) out of it, but it takes 5 columns for each thing I need to track, and I don't think it should.

I tried to incorporate an OR with countifs, but i get a value error. maybe I'm just doing that wrong. I tried a couple ways...this one I thought should work:

Please Login or Register  to view this content.

anyway...here's the sheet:

Please Login or Register  to view this content.

I'd appreciate any help

[mikeTRON]

In the workbook you attached, where do you want this countif + OR statement?

[lumberjim]

I want the answer on the STAFF sheet out in columns AV-AY I'll also need to do another one that counts how many of the same exact thing, but for rows that ALSO have a Y in the split column (DEALS!T*) and subtract (that*.5) from the answer... unless that formula can be combined.

[lumberjim]

I guess it's not possible. I did find a way of speeding it up a bit. I added 25 more columns to the DEAL sheet, and put =IF(OR($F$9=AL8,$G$9=AL8),1,"") and copied it down. Then it's just a matter of entering the COUNTIFS formula for each thing I want to track. AL4 thru BG4 are the salesmans names.

...an important part of this sheet is that I can change personnel easily, so everything leads back to the STAFF page where.... all I have to do is enter the salesman's name once, and his team leader, and his day off... and it updates everywhere else in the entire workbook. The previous version required that I go into the many formulas on each page and change the name, or the tab name etc, if they fired somebody. not... a tolerable amount of maintenance.

what would be damn handy.... and I think I saw something about this.... would be If I could get it to copy down, but go left to right, while I go top to bottom. is there a way of doing that?