How do I hide results until conditions are right to show them?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chris Mitchell - 16 Jul 2007 19:43 GMT

I have three adjacent columns, A, B and C, and wish to sum the total number
of entries of '/' in each column and add the total from column A to column B
to column C.

In A I have '=COUNTIF(H21:H26,$E$229) ' where $E$229 = '/'

In B I have '=COUNTIF(I21:I26,$E$229)+H27' where H27 = the sum of /s in
column A

In C I have '=COUNTIF(J21:J26,$E$229)+I27' where I27 = the sum of /s in
column B (which includes A)

However where there are no entries in B or C I want the totals in these
columns to appear blank, until a '/' is put in either of these columns, when
I want the total in the relevant column to show. I could probably do
something with Conditional Formatting, but is there another/better way?

Reply to this Message

Brian Withun - 16 Jul 2007 21:02 GMT

I've named cell $e$229 "Token"

I've named range $h21:h27 "LeftRange"
I've named range $i21:i27 "MiddleRange"
I've named range $j21:j27 "RightRange"

I've named range $h21:i27 "LeftAndMiddleRange"
I've named range $h21:j27 "AllRanges"

The cell you call A, I will call A1
The cell you call B, I will call B1
The cell you call C, I will call C1

In A1, I have =COUNTIF(LeftRange,Token)
In B1, I have
=IF(COUNTIF(MiddleRange,Token)>0,COUNTIF(LeftAndMiddleRange,Token),"")
In C1, I have
=IF(COUNTIF(RightRange,Token)>0,COUNTIF(AllRanges,Token),"")

The Results:

When MiddleRange has no tokens in it, B1 is always blank
When RightRange has no tokens in it, C1 is always blank

When LeftRange has 3 tokens, MiddleRange has 2 tokens, and RightRange
has 7 tokens, then
A1=3
B1=5
C1=11

When LeftRange has 3 tokens, MiddleRange has 0 tokens, and RightRange
has 1 token, then
A1=3
B1=
C1=4

Is this close to what you intended?

Brian Herbert Withun

Reply to this Message

Brian Withun - 16 Jul 2007 21:05 GMT

> I've named cell $e$229 "Token"
>
[quoted text clipped - 35 lines]
>
> Brian Herbert Withun

++ CORRECTION

When LeftRange has 3 tokens, MiddleRange has 2 tokens, and RightRange
has 6 (not 7) tokens, then
A1=3
B1=5
C1=11

Reply to this Message

Chris Mitchell - 17 Jul 2007 06:44 GMT

Thanks Brian, this looks like it will do exactly what I want.

I did spot your 'error' but had put this down to a typo before seeing your
'correction'.

Thanks again.

Chris.

> I've named cell $e$229 "Token"
>
[quoted text clipped - 35 lines]
>
> Brian Herbert Withun

Reply to this Message