How do I make my CountA formula stop when it reaches a blank cell?

Question

Level 1

8 points

How do I make my CountA formula stop when it reaches a blank cell?

I would like to count the cells following the selected cells in the next column until the first blank cell is reached.

Names	Tasks	Time
John Doe	=CountA(C3:" ") Result=4
Client A	20 mins
Client B	18 mins
Client C	14 mins
Client D	32 mins
Jane Doe	=CountA(C7:" ") Result=3
Client G	14 mins
Client H	42 mins
Client I	17 mins

Then I could just replace the blank cells in this column with the formula to show total work for each employee for the day.

MacBook Air (11-inch Mid 2013), OS X Yosemite (10.10.2)

Posted on Mar 5, 2015 3:17 PM

Reply

Answer 1

Badunit

Level 7

25,802 points

Mar 5, 2015 4:21 PM in response to Clint.B

Here is one way:

Formula in D2 =IF(A2<>"",A2,D1)

Fill down to complete the column.

The formula will auto-populate newly added rows

Formula in B2 =COUNTIFS(D,A2,C,"<>")

Copy/Paste to the other places in column B that require the count.

Hide column D when it is set up.

It will work as long as the names in column A are unique, no multiple John Does or Jane Does.

Reply

Answer 2

Clint.B Author

Level 1

8 points

Mar 5, 2015 4:40 PM in response to Badunit

Thanks for such a quick response!!!
Unfortunately I do not think this will work in my case. Let me modify my chart to more accurately display my information.

Names	Tasks	Time	Date
John Doe	=CountA(C3:" ") Result=4
New York Office Submission	Client A	20 mins	9:22AM 1/5/14
Phone Call	Client B	18 mins	10:15AM 1/5/14
Dropped off	Client C	14 mins	10:40AM 1/5/14
L.A. Contractor	Client D	32 mins	11:00AM 1/5/14
Jane Doe	=CountA(C7:" ") Result=3
Seattle Office referral	Client G	14 mins	8:11AM 1/5/14
Legacy Client	Client H	42 mins	8:28AM 1/5/14
Phone Call	Client I	17 mins	9:53AM 1/5/14

Reply

Answer 3

SGIII

Level 8

35,282 points

Mar 5, 2015 5:40 PM in response to Clint.B

Would you consider changing your setup to something like this?

In B2 of Table 2, copied down: =COUNTIF(Table 1::$A,A2)

In C2, copied down: =SUMIF(Table 1::$A,A2,Table 1::$D)

The entries in column D of Table 1 were entered as shown (20m, 18m, etc) so Numbers would recognized them as durations that can be summed.

SG

Reply

Answer 4

Clint.B Author

Level 1

8 points

Mar 5, 2015 6:29 PM in response to SGIII

Thanks SGIII for the suggestion!

Unfortunately the spreadsheet is a daily data dump that I don't have control over so reordering/rearranging would not be possible prior to download. It would be possible after the download but time consuming which is what I am trying to avoid.
I want to do the sum formula as well. The mins - m is not an issue because my data is numbers only for the time column but the only separating factor between data sections is on the Row with the worker's name and successive blank cells.

If only there was a formula that was =CountA(Range)(Condition=from start until " ") and =Sum(Range)(Condition=from start until " ")

Reply

Answer 5

Badunit

Level 7

25,802 points

Mar 5, 2015 7:04 PM in response to Clint.B

It is not unusual for more details to emerge that make the proposed solution(s) unworkable. But I think the same thing can be done, just a minor change.

Change the formula in D2 to =IF(C2="",A2,D1)

Fill down

You can then drag column D to the right to make it column E and the formulas will adjust.

Reply

Answer 6

SGIII

Level 8

35,282 points

Mar 6, 2015 4:21 AM in response to Clint.B

so reordering/rearranging would not be possible prior to download. It would be possible after the download but time consuming which is what I am trying to avoid.

No reordering/rearranging required before download, and not time-consuming at all after download. Just add Column A to the left of the download data and populate the column quickly with something like this:

In A3, filled down the column:

=IF(D2="",B2,IF(D3="",B3,A2))

In B2 of Table 2, filled down:

=COUNTIFS(Table 1::$A,A2,Table 1::C,"<>"&"")

The SUMIF in C2 stays the same.

SG

Reply