Using Wildcards with SUMPRODUCT

[Aramina]

Hi - I've been trying to work this out for ages, and would be grateful for any help -


I have a spreadsheet that shows sickness - there are 31 columns for each day of the month, and if someone is off sick, an 'S' is entered in the column for the corresponding day.


I have a formula that works out the number of episodes of sickness, so for example, if they're sick from 4th-6th of the month and then again from 21st-30th of the month it will return a result of 2 as they have had two separate periods of being off sick.


This is the formula I have:


=SUMPRODUCT((D6:AH6="S")*1,(C6:AG6<>"S")*1)


This works perfectly, but now I want to be able to put sickness codes into the days, so instead of 'S', it might be 'S1234', 'S32', etc - always the letter 'S' followed by a number - it could be any of a long list of possible numbers.


This means I need a wildcard in the formula, but as I know they don't work with SUMPRODUCT I'm not sure how to do it - I suspect that LEFT might be part of the solution, but I can't work out the correct syntax, so any pointers would be great!


I'm using Excel 2003 by the way

 

[NARAYANK991]

Hi Aramina ,


Try this :


=SUMPRODUCT((LEFT(D6:AH6,1)="S")*1,(LEFT(C6:AG6,1)<>"S")*1)


Narayan

 

[Aramina]

This works perfectly - thank you so much!