calculate the absentees

[sumesh56]

kindly go through the attachment and suggest how to calculate the "absentees" correctly.

[63falcondude]

Your formula in column H has a space after ABS (that is, "ABS "). Correct this by getting rid of that space and then using this:

=COUNTIFS(C2:C16,"<>",H2:H16,"ABS")

Otherwise, you would have to use this:

=COUNTIFS(C2:C16,"<>",H2:H16,"ABS ")

[sumesh56]

thanks a lot for the suggestion. yes ,the space created the problem.your first formula works fine. but what is the difference in the second formula?

i would like to know what does it mean when you use "<>"

[63falcondude]

You're welcome.

The second formula just included the space after ABS whereas the first formula did not. Since you get rid of the space in the formula in column H, you don't have to include it in this one.

"<>" means not blank

"" means blank.

If that answered your original question, please mark this thread as SOLVED.

[sumesh56]

thanks a lot. applied your formula in another instance and i got error. pls see the new attachment.

[63falcondude]

The cells in column C are not being seen as blank for some reason.

Highlight column C > Data > Text to Columns > Finish

Then the formula will work as expected.

[63falcondude]

Or you can change the formula to this:

=SUMPRODUCT((C1:C50<>"")*(H1:H50="ABS"))

[sumesh56]

63falcondude,what a majic!.data- text to columns.( the data was copy pasted ) done . thanks a lot.



But in the real situation, the first formula still gives error while the second one is working fine.
=SUMPRODUCT((C1:C50<>"")*(H1:H50="ABS")) ----this one works
though i have done the text to columns direction, it is not working there. fyi, the content in the columns are the product of some linked sheets. may be that is why it created problem??

[63falcondude]

You're very welcome. Happy to help.

Thanks for the rep!

[sumesh56]

i applied the formula into different situations and getting error. pls see the attachment. instead of "abs" i have put "0"......thanks

[gondal]

see this is what you looking for.!

[63falcondude]

That would be:

D61 =SUMPRODUCT(($C1:$C45<>"")*(D1:D45=0))

or

D61 =COUNTIFS($C1:$C45,"<>",D1:D45,0)

Dragged to the right.