G
Guest
I'm sure something like this has been answered here before, but I haven't
found it yet after an admittedly short search...
My worksheet has a list of tasks across the top (in row 6) and then days
down the left side (in column a). I had a simple =counta(b8:ai8) in the last
column to give me the total tasks completed. (I was just typing an X or
whatever into each cell to indicate it was done.)
Well, now there are three types of days. On "M" days, only the 9 "M" tasks
need to be done. On "B" days, "M" and "B" tasks need to be done. And on all
other days, all 34 tasks need to be done. So I added a row (7) to indicate
what category each task is, "m" "b" or blank. And then than I inserted a
column (B) so I could indicate what kind of day it is.
In other words:
A B C D E F
6 Type task1 task2 task3 task4
7 m b m
8 Wed03/22 b X X
9 Thu03/23 m X X
10 Fri03/24 X X X
11 Sat03/25 m X
I thought I could just use an if statement:
=IF(B8="m",COUNTIF(C8:AJ8, ???? ),IF(B8="b",COUNTIF(C8:AJ8, ????
),COUNTA(c8:AJ8)))
but I can't figure out what to include as the conditional statement in the
Countifs. I want to compare the code for each task (in row 7: b m or blank)
to the code for the current day (in column B) and only count "M" tasks on "M"
days, count "M" and "B" tasks on "B" days and count all tasks on "blank" days.
Can I use sumproduct for that? (It's been a while since I read about
Sumproduct, but I never really understood it to being with...) Or do I need
an array formula? Or something else?
Hope I've explained it well enough. Thanks for any help you can give!
Karin
By the way, when I got a new computer, I didn't copy over all my bookmarks
to various helpful Excel sites, so need to compile a new set of "favorites".
If you have any suggestions, please let me know. (I'm a huge MVPs fans!)
Reply to this or send to karin (at) charterinternet (dot) com
found it yet after an admittedly short search...
My worksheet has a list of tasks across the top (in row 6) and then days
down the left side (in column a). I had a simple =counta(b8:ai8) in the last
column to give me the total tasks completed. (I was just typing an X or
whatever into each cell to indicate it was done.)
Well, now there are three types of days. On "M" days, only the 9 "M" tasks
need to be done. On "B" days, "M" and "B" tasks need to be done. And on all
other days, all 34 tasks need to be done. So I added a row (7) to indicate
what category each task is, "m" "b" or blank. And then than I inserted a
column (B) so I could indicate what kind of day it is.
In other words:
A B C D E F
6 Type task1 task2 task3 task4
7 m b m
8 Wed03/22 b X X
9 Thu03/23 m X X
10 Fri03/24 X X X
11 Sat03/25 m X
I thought I could just use an if statement:
=IF(B8="m",COUNTIF(C8:AJ8, ???? ),IF(B8="b",COUNTIF(C8:AJ8, ????
),COUNTA(c8:AJ8)))
but I can't figure out what to include as the conditional statement in the
Countifs. I want to compare the code for each task (in row 7: b m or blank)
to the code for the current day (in column B) and only count "M" tasks on "M"
days, count "M" and "B" tasks on "B" days and count all tasks on "blank" days.
Can I use sumproduct for that? (It's been a while since I read about
Sumproduct, but I never really understood it to being with...) Or do I need
an array formula? Or something else?
Hope I've explained it well enough. Thanks for any help you can give!
Karin
By the way, when I got a new computer, I didn't copy over all my bookmarks
to various helpful Excel sites, so need to compile a new set of "favorites".
If you have any suggestions, please let me know. (I'm a huge MVPs fans!)
Reply to this or send to karin (at) charterinternet (dot) com