Multiple Criteria for COUNTIF / IF / AND..in excel 2003!

Good morning everyone!

Hopefully you can help! I've been stuck on this one problem for over a week - and usually I can sort out my problems by searching all the threads here, but this has proved to be a fruitless serach this time, as nothing seems to work!

Basically, I have a spreadsheet for work, where we maintain a database of the validity of peoples checks on the teams. There are four teams, spread over four sheets, and the checks expiry date is tracked against the individual - there are 8 differing types of checks that we use. On the overview sheet (Front Cover) I have managed to complete the information for the Team breakdown (mainly thanks for people on here - Thanks guys!). Here is my issue :

Each team is broken down again, into synidcates. There are four syndicates (1-4). The syndicates are split up between the four teams, so when I need to display the information on the overview sheet, I need to search all teams coumn C2:C145 (I have simply gone to C145 incase we increase the size of the teams) of for their syndicate, and then display if their check is due within the next 4 weeks. I have managed to complete the "in date" and "out of Date" data, but for some reason, I cannot get the forumal to work with the "due in 4 weeks". Apparently it would be simple with COUNTIFs, but I'm using Excel 2003 - ARGH!

So, the formula needs to read C2:C145 from all sheets, checking which syndicate is displayed, and then decide if the relevant check is due within 4 weeks of todays date. I have tried having "cheat" boxes hidden away with all the information split down, but that doesn't seem to help. Apparently it may have to involve VBA, which would be a potential non-starter from me!

I've included a (very) stripped down example of what I am working on - The original contains a lot of sensitive data, so I mocked up what it would look like - enough for someone to understand what I mean / need.

I REALLY hope someone can help....PLEASE!!!! Many, many thanks everyone!

I have used SUMPRODUCT throughtout, but it doesn't seem to look for the last date. Now matter how I have set it up!

The type of thing I have used is similar to this:

=SUMPRODUCT('TEAM A'!C2:C145="1",('TEAM A'!E2:E145=">"&TODAY()+31)).

This constantly returns 0 (or an error), no matter what. It could be as simple as my logic is wrong. It does need to count how many people have their checks due within the next 31 days, so I also tried to use COUNTIF, but again, it wouldn't work.

Try this
=SUMPRODUCT(('TEAM A'!C2:C145=1)*('TEAM A'!E2:E145>=TODAY()+31))

With the use of helper columns I came up with this. Not very elegant but seems to work.

Windy

Originally Posted by Kaper

The clou is here (based on my sample notation):

Formula:

Please Login or Register  to view this content.

not:

Formula:

Please Login or Register  to view this content.

another option:

Formula:

Please Login or Register  to view this content.

in simple words - you have to supply sumproduct with numbers not table of boolean values. The easiest way to achieve this is by forcing excel to do type conversion for us.

I've seen such approach in your file so expected you are aware of this.

I don't really understand your formula - I'm not searching for anything in coumn A or B - I'm not sure how to get this to read my columns?

Originally Posted by kvsrinivasamurthy

Try this
=SUMPRODUCT(('TEAM A'!C2:C145=1)*('TEAM A'!E2:E145>=TODAY()+31))

I've tried this also, and it still returns "0" - could this be down to formatting of the actual cell with the date?

I am not clear what is required. Try this in C10, then dragged horizontally.

Please Login or Register  to view this content.

I am not clear what is required. Try this in C10, then dragged horizontally.

Please Login or Register  to view this content.