COUNTIF Multiple cells in column

**swaza** · 09-09-2015, 05:19 AM

I have a timesheet that include staff of different disciplines and their hours. I am count the amount of staff in each day by purely using function to count any that begin with 0 - Function used: =COUNTIF(B14:B54,"0*") This returns the total of 10 which is correct. This however is split into 4 manager and 6 staff and would like to split this by using same count but for cells that those disciplines appear. For example I tried;
=COUNTIF(B18,B27,B28,B40,B48,"0*") but this didn't work.

Can someone tell me how I select random cells within same column. The aim is to have total number of managers and staff in each day of which I can then add conditional formatting to when falls below required level.

Any advice would be gratefully received.

**XOR LX** · 09-09-2015, 05:30 AM

Hi.

Is there not another column which lists whether they are "manager" or "staff"? If there is, you could simply include that by extending your function to COUNTIFS.

Regards

**Tony Valko** · 09-09-2015, 08:08 AM

Originally Posted by swaza

=COUNTIF(B18,B27,B28,B40,B48,"0*")

but this didn't work.

Something like this:

=SUM(COUNTIF(INDIRECT({"B18","B27","B28","B40","B48"}),"0*"))

But that's not very robust.

**swaza** · 09-09-2015, 09:11 AM

That worked. Thank you. However is there a simple way now to apply the formula across all columns (days of the week)???

**Tony Valko** · 09-09-2015, 09:15 AM

I think you need to post a SMALL sample file so we can see what you're trying to do.

We don't need a file with hundreds of rows and dozens of columns worth of data. About 20 rows and a few columns worth of data is plenty.

Make sure you tell us what results you expect.

COUNTIF Multiple cells in column

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

COUNTIF Multiple cells in column

Re: COUNTIF Multiple cells in column

Re: COUNTIF Multiple cells in column

Re: COUNTIF Multiple cells in column

Re: COUNTIF Multiple cells in column

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Multiple Countif's on data placed in 1 column

[SOLVED] Countif multiple criterai within 1 column

COUNTIF Multiple Column Criteria

Trying to COUNTIF when multiple criteria are met on multiple column data set

COUNTIF, AND, OR Statements to multiple cells with multiple condtions

Countif with multiple criteria:Column A Column B

[SOLVED] Using Countif with multiple criteria in the same column.

Bookmarks

Bookmarks

Posting Permissions