1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: count instances that occur in multiple rows/columns after criteria met

Discussion in 'Business Applications' started by rbeaufor, Jun 13, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. rbeaufor

    rbeaufor Thread Starter

    Joined:
    Feb 7, 2003
    Messages:
    163
    Excel 2000

    I have created a calender ( actually a schedule) in excel which has multiple rows columns (A9:O36). The schedule shows where everyone works that day (eg billy on fries, johnny on grill, susie on cashier)

    At the bottom I want to create one box which shows what days each worked and where

    ..................Fries | Grill | Cashier |

    Billy
    Johnny
    Susie


    I've tried Sum. sumif, countif, if-then even an array...


    Can't seem to do it or find the answer through searching
    The closest I've come is the array, but I don't know how to make it work for a range of cells that is not a column


    Any thoughts would be appreciated.
     
  2. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    So, are the calendar dates across the top & work areas down the side?

    If row 10 was for fries and dates started in B9 (so that B10 was the space for 14th June, B11 for 15th June, etc.), then:

    =COUNTIF(10:10,"Billy")

    ought to do it (where 10:10 means row 10).

    Also, you could substitute "Billy" with a cell reference containing the actual text "Billy" (or "Johnny" or "Susie").

    HTH
     
  3. rbeaufor

    rbeaufor Thread Starter

    Joined:
    Feb 7, 2003
    Messages:
    163
    bomb, thanks for looking into this.

    across the top (not row one ) is sunday through saturday
    2 colums and 6 rows make up each day such that July 1 is a7, July 2 is c7, etc
    Note that July 8 is A13, July 9 c13

    grill is row 10,16,22,28 and 34
    fries is row 11,17,23,29 and 35

    you probably get the picture...

    I can do a seperate countif for each row, but I'd like to do one for ALL the rows that include fries

    Johnny may work grill on 3 days a week and fries on 2

    At the bottom of the schedule I'd like to be able to tabulate how many days en total for the month.


    (hope thats not too confusing)

    Any additional thoughts???
     
  4. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Possibly an additional thought, singular. :)

    Never tried SUMPRODUCT over an array before. Try the attached & see if it works.
     

    Attached Files:

  5. rbeaufor

    rbeaufor Thread Starter

    Joined:
    Feb 7, 2003
    Messages:
    163
    bomb, again thanks for looking closer at this...

    I've been fiddling with it for about an hour and just can't get it to work for me.

    Take a closer look and let me know what I'm doing wrong.

    I do think an array is the answer, I just cannot get one to work
     

    Attached Files:

  6. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Ah, OK. Well, your actual layout isn't compatible with my suggestion, or vice versa. Sorry. :(

    I wouldn't say an array isn't do-able. But at this time of the AM, I'll stick with the obvious. Which is multiple COUNTIFs, such as:

    =COUNTIF(11:11,"Bolton")+COUNTIF(17:17,"Bolton") (etc.)

    HTH
     
  7. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Sorry, didn't have time to look at SUMPRODUCT before ; but it does seem to work.

    In B40:

    =SUMPRODUCT(($A$7:$A$36=B$39)*($A$7:$N$36=$A40))

    Copy it across to C40 & down to C43 to get:

    Totals|Fries|Grill
    Billie|8|8
    Bolton|7|8
    Ellie|8|8
    Sallie|8|7
     
  8. rbeaufor

    rbeaufor Thread Starter

    Joined:
    Feb 7, 2003
    Messages:
    163
    Thanks alot !!!!!


    It DOES seem to work.

    I was about one step off, I couldn't get my mind around the concept of just using one row for the condition, but the setup of the calender lends itself to this kind of use.


    I REALLY appreciate it, I've spent countless hours racking my brain on how to make that happen....obviously I am pretty unfamiliar with arrays ANd sumproduct, but I have a much better understanding now.


    Tell me (though I would likely never set it up like this) what if there were 20 different areas.....fry 1, fry 2, fry 3, , grill 1, grill 2, grill 3, cashier1, cashier 2, cashier 3 , manager, maintenance, electrician, trash guy, AC guy, etc but only 5 worked on any particular day such that the calender had much the same format, BUT, the rows weren't standard eg fry3, grill2, trash guy could all be on the same row.

    is there anyway to set the initial condition in the array to look over multiple rows and columns and do the same tally's for days working which station ?

    Or would I need to have a different row for each station??


    This is simply my curiosity, don't expend too much brain power if the answer is not immediately evident.


    Thanks again
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/583966

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice