I NEED AN ARRAY FORMULA TO COUNT SALES FOR INDIVIDUALS. THAT PART WASIS EASY BUT IF THERE ARE 2 SALES PEOPLE ON THE SALE THEN I NEED TO COUNT THE VALUE AS 1/2 SALE. I HAVE A COLUMN FOR NEW OR USED, A COLUMN FOR SALES PERSON #1 AND ANOTHER FOR SALES PERSON #2 SO I WILL HAVE 3 CRITERIA IN THE FORMULA.
I ALSO NEED ANOTHER FORMULA TO COUNT TOTALS FOR OTHER SALES PEOPLE THAT ARE NOT IN A LIST OF PEOPLE AND AGAIN WITH 1/2 (SPLIT SALE) AND 1 (WHOLE SALE). =SUM(IF((LOG!$E$6:$E$261="N")*(LOG!$H$6:$H$261,LOG!$I$6:$I$261<>"ROBERT","JOHN","MIKE","JIM","ANDY"),1))
=SUM(IF((LOG!$E$6:$E$261="N")*(LOG!$H$6:$H$261="ROBERT"),1)) - THE EASY PART WORKS FINE UNTIL I NEED TO SEARCH 2 COLUMNS FOR THE NAMES.
=SUM(IF((LOG!$E$6:$E$261="N")*(LOG!$H$6:$H$261,LOG!$I$6:$I$261="ROBERT"),1)) - DOES NOT WORK TO SEARCH FOR ROBERT TO BE IN EITHER COLUMN.
I ALSO NEED THIS. I WANT TO SEARCH FOR A SALES PERSON'S NAME IN COLUMN H BUT IF THE SAME ROW IS POPULATED WITH ANY SALES PERSON IN COLUMN I THEN IT WOULD GIVE BOTH SALES PEOPLE 1/2 A SALE ON A SEPARATE SUMMARY REPORT WORKSHEET LIKE THIS.
A. Sold Units New Used TOTAL TRACK
John 2.5 2 4.5
Jim 2 3 5
Robert 2 2.5 4.5
Andy 5 3 8
Mike 6.5 2 8.5
Other Sales People 5.5 0 5.5
Open
Open
Total Dept Sales
ANY HELP WOULD BE APPRECIATED. PLEASE!
Bookmarks