Multiple Criteria with a date range/month

**philllipoosis** · 01-13-2011, 05:22 AM

Hi guys,

Iv managed to use the formula =SUMPRODUCT(--(MONTH(Register!$B$2:$B$200)=10),--(YEAR(Register!$B$2:$B$200)=2010)) successfully to count the number of 'V notes' created in a given month/year.

If you look at my attachment you will see that when i modify the formula to try and count the number of 'V notes' closed in a given month it wont work. Is this because alot of the rows in this coloum are blank? suggestions for this please?

I also need to break down how many were opened and closed each month into Internal and External segments so i need to modify the formaula to count cells in a given month which have 'Int' and then 'Ext' in another coloum. Please see my attachment.

Really need your helps guys, thanks

**DonkeyOte** · 01-13-2011, 05:29 AM

Re: Internal etc...

Please Login or Register  to view this content.

Re: Closed... you have an invalid date value (D39) and both MONTH & YEAR explicitly coerce - if the value can not be coerced to Number a #VALUE! will result

To cater for the above you must either a) amend the offending value [advised] and/or b) revert to a slightly slower comparison method which avoids explicitly coercing the values, eg:

Please Login or Register  to view this content.

**philllipoosis** · 01-13-2011, 05:40 AM

Lol i cant believe it was so easy to solve, iv spent hours pulling my hair out!! Iv change the date to a valid one and all the formulae work great, thanks very much

**philllipoosis** · 01-13-2011, 12:25 PM

=SUMPRODUCT(--(MONTH('Short Term Issues'!$A$2:$A$100)=11),--(YEAR('Short Term Issues'!$A$2:$A$100)=2010),--('Short Term Issues'!$D$2:$D$100="Leaking"))

Im using the above formula to count rows which have the information 'Leaking' contained in column D:D where the date raised in column A:A is within a certain month, in this case 'Nov2010' - Ill be duplicating the formula for subsequent months, and differant key words in other cells

My problem is that this formula only counts cells which are an exact match of the keyword. Can this be modified so that it counts cells where other words are present along with the keyword?? I have tried using a wildcard type function but iv been unsuccessful so far.