Closed Thread
Results 1 to 3 of 3

3-4 countif criteria that can count as 1/2 instead of 1 for true and 1 as false

  1. #1
    Registered User
    Join Date
    09-01-2012
    Location
    oklahoma
    MS-Off Ver
    excel 2010
    Posts
    4

    3-4 countif criteria that can count as 1/2 instead of 1 for true and 1 as false

    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!

  2. #2
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,421

    Re: 3-4 countif criteria that can count as 1/2 instead of 1 for true and 1 as false

    http://www.excelforum.com/excel-form...-as-false.html

    http://www.excelforum.com/excel-form...-as-false.html

    duplicate posts.
    - i.s.z -
    CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
    Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
    All good ideas are courtesy resources from this forum as well as others around the web.
    - e.o.m -

  3. #3
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: 3-4 countif criteria that can count as 1/2 instead of 1 for true and 1 as false

    Welcome to the Forum, unfortunately:

    This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.

    Thread Closed.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1