Calculating a aariable amount or row

[Guest]

I have a numerous sets of data that varies from set to set in the amount of
rows each contains (one set will contain 2135 rows and one will have 3784
rows, etc.). Each set has 12 columns. I have some formulas set up that do
various calcutions on the data.

As things are now I am having to copy and drag the formula's down to match
the end of the data. I also have to enter a formula that calucates the sum
of one of the columns. This is very time-consuming.

Is there a function/formula/option that will fill in my formula all the way
to the end of the rows of data, no further? Is there a
function/formula/option that will then calculate a the sum of a column of
"x"-amount of rows?

Assume the sum of column E.

 

[Carim]

Hi,

Could you share with us the formula, to make appropriate suggestions
....

Cheers

Carim

 

[Guest]

Hi,

Could you share with us the formula, to make appropriate suggestions
....

Cheers

Carim

=ISNA(MATCH(D2,$P$2:$P$2500,FALSE))

is the formula that is in cell y2.

=ISNA(MATCH(P2,$D$2:$D$2500,FALSE))

is the formula that is in cell z2.

The formula checks to see if a sales receipt number from one list is on the
other. I use both lists, one from the store and one from company database,
to reconcile that month's sales.

I sum column E which is the total sales for the month to see how far off the
two reports are. When I find a sales receipt that needs to be added or
subtracted to one report or the other the total sales sum will get closer.
When they match I know that both lists contain the necessary sales receipts,
I know that I am through reconciling.

Some reports contain 3500 rows and others contain 5000+ rows.

 

[Carim]

Hi,

I would suggest to look into two possibilities to pick the one which
best suits your needs.

1. If the number of rows is different in each column, but known and
therefore stored somewhere, I would recommend using OFFSET (Nbrows, 0)
in your formulas

2. If the above is not possible, then I would use Define Name to locate
the last cell and then use it in your formulas

Hope this is clear enough
Cheers
Carim

 

[Carim]

Oops ...

Missed the example for the formula :

=ISNA(MATCH(P2,$D$2:Offset($D$2,2499,0),FALSE))

Carim

 

[Carim]

Oops ...

Missed the example with the formula :

=ISNA(MATCH(P2,D2:OFFSET(D2,COUNT(D365536),0),FALSE))

Cheers
Carim