Way to write a sum without repeating the same word?

mortgageman said:

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

Code:

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

Click to expand...

T. Valko said:

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"))

Click to expand...

mortgageman said:

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

Click to expand...

mortgageman said:

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

Click to expand...

Ron Coderre said:

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?

Click to expand...