How to represent a column in Excel

K

kOdreaming

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
 
E

Earl Kiosterud

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))}
Click to expand...

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.
 
O

Ole Karlsen

Hi!

Could'nt you write it like this?:

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

/Ole
 
R

Roger Govier

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))
 
Top