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

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

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

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