Way to write a sum without repeating the same word?

[mortgageman]

I am wondering if there is a better way to write the following formula (i.e. instead of repeating the "Absent" 20 times - which is what I am going to have to do, and then do it again for "Late")

Any help would be appreciated here.

Thank you,

Gene Klein

=--(B2="Absent")+--(D2="Absent")+--(F2="Absent")+--(H2="Absent")+--(J2="Absent")

 

[wigi]

Is there a particular reason why you skip a column each time?

A countif formula would be much more interesting in my opinion.

 

[Ruddles]

Stick the word "Absent" in a cell somewhere and use the cell reference?

 

[mortgageman]

Wigi - I "skip" columns because other stuff (like tests) happens in between the dates. I would rather not restructure the spreadsheet, but if I have to (so as to use countif's limitations) I will. I am hoping for a solution that has Excel accomodate me and not the other way around.

Ruddles - I don't follow what you mean. Can you be more specific?

Gene Klein

 

[T. Valko]

I am wondering if there is a better way to write the following formula (i.e. instead of repeating the "Absent" 20 times - which is what I am going to have to do, and then do it again for "Late")

Any help would be appreciated here.

Thank you,

Gene Klein

=--(B2="Absent")+--(D2="Absent")+--(F2="Absent")+--(H2="Absent")+--(J2="Absent")

In your current formula there's no need for the double unary.

=(B2="Absent")+(D2="Absent")+(F2="Absent")+(H2="Absent")+(J2="Absent")

That's fine if the number of cells involved is not a lot. You wouldn't want to do that with 100 cells!

If the cells between the target cells do not contain the the word absent then you can just use a COUNTIF with a contiguous range. Like this:

=COUNTIF(B2:J2,"absent")

If the cells between might contain the criteria to be counted for and there are a lot cells involved then you can use something like this:

=SUMPRODUCT(--(MOD(COLUMN(B2:J2)-COLUMN(B2),2)=0),--(B2:J2="absent"))

 

[mortgageman]

In your current formula there's no need for the double unary.

=(B2="Absent")+(D2="Absent")+(F2="Absent")+(H2="Absent")+(J2="Absent")

That's fine if the number of cells involved is not a lot. You wouldn't want to do that with 100 cells!

If the cells between the target cells do not contain the the word absent then you can just use a COUNTIF with a contiguous range. Like this:

=COUNTIF(B2:J2,"absent")

If the cells between might contain the criteria to be counted for and there are a lot cells involved then you can use something like this:

=SUMPRODUCT(--(MOD(COLUMN(B2:J2)-COLUMN(B2),2)=0),--(B2:J2="absent"))

I tried your SUMPRODUCT formula Biff (Thank you BTW) but the result was 1, even though there were two "Absent"s (both of which I wanted to count) in the range.

Gene Klein

 

[mortgageman]

I tried your SUMPRODUCT formula Biff (Thank you BTW) but the result was 1, even though there were two "Absent"s (both of which I wanted to count) in the range.

Gene Klein

I just realized: If I am not worried about "false positives" in the range, I can just use countif.

Gene Klein

 

[T. Valko]

I tried your SUMPRODUCT formula Biff (Thank you BTW) but the result was 1, even though there were two "Absent"s (both of which I wanted to count) in the range.

Gene Klein

Here's a small sample file that demonstrates the SUMPRODUCT formula.

ZZZmortgageman.xls 15kb

http://cjoint.com/?AGFq102dPST

 

[Ron Coderre]

Here's a regular formula alternative that tests every other column (B2, D2, etc) for the word "absent" and returns the count of matches:
=SUM(COUNTIF(OFFSET(B2,,{0,2,4,6,8}),"absent"))

Note: The OFFSET function is volatile and calculates whenever any cell in the workbook calculates. But, when used sparingly and judiciously, it can be very useful.

Does that help?

 

[mortgageman]

Here's a regular formula alternative that tests every other column (B2, D2, etc) for the word "absent" and returns the count of matches:
=SUM(COUNTIF(OFFSET(B2,,{0,2,4,6,8}),"absent"))

Note: The OFFSET function is volatile and calculates whenever any cell in the workbook calculates. But, when used sparingly and judiciously, it can be very useful.

Does that help?

The offset wouldn't help here since the "space" between my dates is not regular. However, like I said above, none of the columns inbetween my dates would have anything close to "Absent" or "Late" etc, so the plain vanilla countif will work perfectly. Thank you to all who posted.

Gene Klein