How to represent a column in Excel

[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

 

[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))}

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.

 

[Ole Karlsen]

Hi!

Could'nt you write it like this?:

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

/Ole

 

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