How to represent a column in Excel

Hi, everyone

I currently have a formula.

{=SUM(IF((Data!G2:G75="Verified")*(Data!K2:K75<=7),1,0))}


However, the length of both column G and K are not fixed.
The data is retrieved from a remote database.

Is there any other way to represent it ?


Thanks

Earl
Fri Feb 09 10:05:51 CST 2007

One quick-and-dirty way is to just do the whole column, x`like:

> {=SUM(IF((Data!G2:G65536="Verified")*(Data!K2:K65536<=7),1,0))}

It won't be appropriate for Excel 2007, but is there any chance at all you'll actually fill
the sheet to row 65536? It's also considered somewhat sloppy, but you'll have long since
been promoted. It works just fine.

You can set up what's called a dynamic range. There are restrictions. Search for that if
you want to go that way.

--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
<kOdreaming@gmail.com> wrote in message
news:1171033975.589426.62480@j27g2000cwj.googlegroups.com...
> Hi, everyone
>
> I currently have a formula.
>
> {=SUM(IF((Data!G2:G75="Verified")*(Data!K2:K75<=7),1,0))}
>
>
> However, the length of both column G and K are not fixed.
> The data is retrieved from a remote database.
>
> Is there any other way to represent it ?
>
>
> Thanks
>

Ole
Fri Feb 09 14:15:29 CST 2007

Hi!

Could'nt you write it like this?:

{=SUM(IF((Data!G:G="Verified")*(Data!K:K<=7),1,0))}

/Ole

<kOdreaming@gmail.com> wrote in message
news:1171033975.589426.62480@j27g2000cwj.googlegroups.com...
> Hi, everyone
>
> I currently have a formula.
>
> {=SUM(IF((Data!G2:G75="Verified")*(Data!K2:K75<=7),1,0))}
>
>
> However, the length of both column G and K are not fixed.
> The data is retrieved from a remote database.
>
> Is there any other way to represent it ?
>
>
> Thanks
>

Roger
Fri Feb 09 17:06:01 CST 2007

Hi

Since column K is numeric, use that column to count the rows with data.
Create 2 Dynamic ranges.
Insert>Name>Define> Name RngK
Refers to =Data!$K$2:INDEX(Data!$K:$K,COUNT(Data!$K:$K)+1)

Name RngG
Refers to =Data!$G$2:INDEX(Data!$G:$G,COUNT(Data!$K:$K)+1)

Formula for result
=SUMPRODUCT((rngG="verified")*(rngK<=7))


--
Regards

Roger Govier


<kOdreaming@gmail.com> wrote in message
news:1171033975.589426.62480@j27g2000cwj.googlegroups.com...
> Hi, everyone
>
> I currently have a formula.
>
> {=SUM(IF((Data!G2:G75="Verified")*(Data!K2:K75<=7),1,0))}
>
>
> However, the length of both column G and K are not fixed.
> The data is retrieved from a remote database.
>
> Is there any other way to represent it ?
>
>
> Thanks
>