Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
using countif
I have a calendar that tracks absenteeism
We have { LOA / WSIB / S&A / sick / E-leave } my calendar is quarterly from A13 to W27 on sheet 2 I want to keep a run total of all the absents can this be done with one formula -- grizz |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
using countif
Yes. But it is hard to explain without knowing what's in your rows and
columns so I will be more general. If you set up something like this then the formula in cell F2 will be =COUNTIF(B2:E2,"LOA") That will tell you how many LOA John has. You can have more columns with Totals for all the other absence categories. Then you can sum them at the bottom. A B C D E F 1 Day 1 Day 2 Day 3 Day 4 Total LOA 2 John 3 Mike LOA LOA 4 Dave -- Allllen "grizzly6969" wrote: I have a calendar that tracks absenteeism We have { LOA / WSIB / S&A / sick / E-leave } my calendar is quarterly from A13 to W27 on sheet 2 I want to keep a run total of all the absents can this be done with one formula -- grizz |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
using countif
=SUMPRODUCT(--(A13:W27="a")--(A13:W27="ela")--(A13:W27="s&a")--(A13:W27="wsib"))
Thanks Alllen -- this formula did the job -- grizz "Allllen" wrote: Yes. But it is hard to explain without knowing what's in your rows and columns so I will be more general. If you set up something like this then the formula in cell F2 will be =COUNTIF(B2:E2,"LOA") That will tell you how many LOA John has. You can have more columns with Totals for all the other absence categories. Then you can sum them at the bottom. A B C D E F 1 Day 1 Day 2 Day 3 Day 4 Total LOA 2 John 3 Mike LOA LOA 4 Dave -- Allllen "grizzly6969" wrote: I have a calendar that tracks absenteeism We have { LOA / WSIB / S&A / sick / E-leave } my calendar is quarterly from A13 to W27 on sheet 2 I want to keep a run total of all the absents can this be done with one formula -- grizz |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
using countif
Try this:
=SUM(COUNTIF(A13:W27,{"a","ela","s&a","wsib"})) -- Biff Microsoft Excel MVP "grizzly6969" wrote in message ... =SUMPRODUCT(--(A13:W27="a")--(A13:W27="ela")--(A13:W27="s&a")--(A13:W27="wsib")) Thanks Alllen -- this formula did the job -- grizz "Allllen" wrote: Yes. But it is hard to explain without knowing what's in your rows and columns so I will be more general. If you set up something like this then the formula in cell F2 will be =COUNTIF(B2:E2,"LOA") That will tell you how many LOA John has. You can have more columns with Totals for all the other absence categories. Then you can sum them at the bottom. A B C D E F 1 Day 1 Day 2 Day 3 Day 4 Total LOA 2 John 3 Mike LOA LOA 4 Dave -- Allllen "grizzly6969" wrote: I have a calendar that tracks absenteeism We have { LOA / WSIB / S&A / sick / E-leave } my calendar is quarterly from A13 to W27 on sheet 2 I want to keep a run total of all the absents can this be done with one formula -- grizz |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I use a countif function according to two other countif fu. | Excel Worksheet Functions | |||
edit this =COUNTIF(A1:F16,"*1-2*")+COUNTIF(A1:F16,"*2-1*") | Excel Discussion (Misc queries) | |||
COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
countif | Excel Discussion (Misc queries) | |||
COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions |