Crazy formula

Hi there,

Thanks for taking the time to check out my thread!

Right, this one is a nightmare for me! here we go:-

Lets say i have two worksheets, sheet 1 has a column called "system codes" (i.e. LUF, LDU, LWI). Sheet 1 also has a column retiral date (i.e. 12-feb-2006). There are other columns but they dont really matter for this calculation.

I require a calculation that looks a system code (i.e. LUF) and checks the retiral date column for all LUF which have a retiral date of less thas one year from today. The output i need is the number of LUF's with a retiral date less than one year from today.


I hope i have explained myself clearly.

Thanks again for your help

Kind regards

SWM

Lets see if I have this right:

You have a block of data relating listing items each of which has system code and a retiral date. You want to count the number of items with a system code of say LUF and an expiry date less than 1 year away.

If cell A1 contains the code you want to count on (e.g. LUF), column F contains the code for the individual items and column G contains their expiry dates then

=SUMPRODUCT((F2:F7=A1)*(G2:G7<=TODAY()+365))

should work

Thanks very much for the reply; however, i am a beginner to excel and i can't modify your formula. Do you mind helping me again by modifying it to suit my spreadsheet please?

The formula will go into sheet 2 - System codes are in column A of this sheet (LUF = row 18).

Sheet 1 (named - Register) holds my data, column Q, Rows 2-2000 contain all system codes (i.e. LUF) and column AB, rows 2-2000 contain the retiral dates.

I tried modifying your calc, but as you can see, i'm hopless!

See:-

=SUMPRODUCT((Register!Q:Q=LUF)*(Register!AB:AB<=TODAY()+365))


Thanks again for all your help

SWM

Not far off!

This should do it

=SUMPRODUCT((Register!Q$2:Q$2000=Sheet2!A18)*(Register!AB$2:AB$
2000<=TODAY()+365))

copy an paste this formula into B18 on sheet2. You can then copy it to all the other cells in column B to give the totals for all codes.

HI again,

Sorry about this but i used this fomula and it didnt seem to work? dont suppose you know what i could be doing wrong do you? I keep getting - #DIV/0!

Thanks

SWM

=SUMPRODUCT((Register!Q$2:Q$2000=A18)*(Register!AB$2:AB$2000<=TODAY()+365))

There must be something in the formatting - probably of the dates.

Excel is great for this kind of thing!!! You can't get my formula to work and I can't duplicate your problem!!

I suggest you try re-entering some of the dates (dd/mm/yy) and see if this helps. Then try checking that the codes are exactly as they appear. It might be easier to set up a dummy spreadsheet with a small amount of carefully entered data to test each part of the calculation (ie only using 1 code, then two etc). Alternatively give me an email address and I will send you the spreadsheet I set up to check the formula.

Thanks again,

It worked when i created a simple spreadsheet but for some reason i cant get it to work in my real spreadsheet, i have tried changing the date formats but nothing seems to work, dont suppose you have any ideas do you?

Thank you

SWM

Chances are there is something funny in your date column.

you could try changing the format of the dates and seein gif that helps. Alternatively try using another column filled with =value(datecolumn) to see if anything is odd.

Hi Alex,

Again, thank you for all your help with help, i dont suppose you could give me a quick hand if i was to e-mail you my file could you please?

Thanks

SWM