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

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

Not open for further replies.

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

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

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)

4. ### bomb #21

Joined:
Jul 1, 2005
Messages:
8,546

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

#### Attached Files:

• ###### rbeaufor.xls
File size:
13.5 KB
Views:
225

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

File size:
25 KB
Views:
153
6. ### 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

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

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

As Seen On