Counting Cells with Variable Reference

[yensid]

Hi...I've been working on this problem for awhile and have been unable to figure out a solution. I have a list of data that is generated daily on one sheet, on a second sheet I have a list of customers and the total number of product received from them.

What I am looking for is a way for the formula to automatically count the raw data based on the Total In count listed next to the customers name.

The raw data is generated daily and the customer names, counts, and number of customers vary each day.

I have been using the following formula as an example: "=IF(B2=0,0,COUNTIFS(INDIRECT("'RAW DATA'!A2:A52"),">2000000",INDIRECT("'RAW DATA'!A2:A52"),"<3000000"))"

However for each customer I have to go in and change the cell references. Is there some way where the formula can automatically count cells based on the value in another cell or combination of cells?

I've attached a sample workbook as an example.

Thanks for any help!

-yen

[DonkeyOte]

I noticed you're using INDIRECT - are you physically removing and inserting the Raw Data sheet each day ?

[yensid]

Yes, in a way. In the actual workbook there is a sheet where you change the date and or time and the data on the raw data sheet will automatically change. This data is polled from a bar code scanner and stored in a external data source. I've attached a picture of the screen that the date and time are changed via.

I also noticed I posted this with the wrong window to an incorrect forum so please feel free to move to the appropriate section (Excel General)

Thanks.

-yen

[DonkeyOte]

Having looked at your file ... if you insert a column between B & C (so old C becomes D and new C is blank) you can keep things simple... eg

Please Login or Register  to view this content.

The above can be applied across matrix D2:G11

Hope that helps

(Thread moved to Worksheet Functions Forum)

[DonkeyOte]

My point re: INDIRECT is that if you're not deleting the sheet itself you don't need to use INDIRECT (it is a Volatile function)

A non-volatile version of that in the prior post would be:

Please Login or Register  to view this content.

[DonkeyOte]

yensid, apologies, but there was a typo in the prior example - the first INDEX should use 2+SUM(B$1:B1) rather than 1+SUM(B$1:B1) -- this is to account for first row of data being in row 2 rather than row 1. I have modified the prior examples accordingly.

[yensid]

Donkey:

Thanks so much it works like a charm...two months of tinkering around solved in 30 minutes ;o)

I understand the countif and index functions, but have never used 2+SUM or "<"&IF before...Is it possible for you to give me a quick explanation for them? Or if there is an online explanation point me in the proper direction?

Thanks.

-yen

[DonkeyOte]

In essence we're simply using a standard COUNTIF and a COUNTIF as you know works along the lines of:

Please Login or Register  to view this content.

In our case our formula (for D2) looks like

Please Login or Register  to view this content.

So you can see (by means of colour coding) that we use 2 INDEX functions to create the range and a subsequent IF in conjunction with & and < operator to create the criteria
(the last bit in Italics which sits outside of the COUNTIF is to ensure we're displaying only "periodic" movement - inappropriate term here in truth but one that epitomises the general principle)

So - range - in more detail...

Please Login or Register  to view this content.

You can see the INDEX calls are separated by a colon indicating we're using INDEX in this context to create range references - this may be different to how you've seen it used before ?

INDEX is a pretty flexible formula, eg:

Please Login or Register  to view this content.

could be used to return contents of A10 or

Please Login or Register  to view this content.

could be used to create a range A10:A20

make sense ?

In your case the start row & end row for the range is determined by the rolling sum of the values in Column B, for ex. B2 = 51 ... this figure states we should look only at the first 51 rows of data in the table... we know first row of data is row 2.

So to establish starting cell we have:

Please Login or Register  to view this content.

which says column is A and row is to be 2 + sum of prior values (ie excluding current row) in B which for row 2 will be 0 given B1 is non-numeric

To establish end row we have:

Please Login or Register  to view this content.

which says column is A and row is to be 1 + sum of all values in B up to and including the current row which for row 2 will be 52 (1+51)

Thus we end up in essence with a range that says

Please Login or Register  to view this content.

When we move to the next row down in our records the start row & end row will update accordingly, eg:

B2: 51
B3: 168

So our start range for the COUNTIF in row 3 of our results table will be:

Please Login or Register  to view this content.

And end row

Please Login or Register  to view this content.

Does that make sense ?

The criteria for the COUNTIF we set to be the value in row 1 in the column to the right of the cell containing the formula (ie less than next boundary), however, when it comes to the last column we have a problem given there is no limit in the next cell - to account for this we use the IF

Please Login or Register  to view this content.

so we say if the value in row 1 of the column to the right is non-numeric use 5000000 else use the value in row 1 of the column to the right.

to get this into a readable string we concatenate the "<" operator with this value by means of & ....

Please Login or Register  to view this content.

Obviously this means in each column we're counting how many records are less than the limit ... but this will obviously include values that may be included in prior boundaries so we need to subtract the prior totals from the COUNTIF total

Please Login or Register  to view this content.