Countif/sum/count forumla help

[mecheet]

I have the below formula which works out when people are available.

=(COUNT(‘Holidays’!$A$42:$A$46)-SUM(‘Holidays'!$B$42:$B$46)-COUNTIF(‘Holidays’!$B$42:$B$46,"?*"))*C5

so if the cell is blank it says that availble and this number is then mulitplied by C5, if there is any text it is discounted from the availablility.

Now i need to change the formula to only look for cells that contain the following, 1, 0.5, M, U, P, T

there are many other entries which will be in the cells i just need it to be specific to discount if the above is placed.

 

[Domenic]

Unclear, so here's a guess...

=(COUNT(Holidays!$A$42:$A$46)-SUMPRODUCT(--ISNUMBER(MATCH(Holidays!$B$42:$B$46,{1,0.5,"M","U","P","T"},0)))-COUNTIF(Holidays!$B$42:$B$46,"?*"))*C5

If this is not what you're looking for, try providing a small sample of the data, along with the expected results.

 

[mecheet]

thanks but doesnt work.

what im looking for is on the holidays tab in column A has a value of 1 from row 42 - 46, so it would be 5 for 5 working days. then in column B it has the many letter options, but my formula only takes away the value if its a number this is where i need it to also inlcude the other letters. then this is multiplied by hours in a day to give hours for the week.

<TABLE style="WIDTH: 26pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=34 border=0 x:str><COLGROUP><COL style="WIDTH: 12pt; mso-width-source: userset; mso-width-alt: 585" width=16><COL style="WIDTH: 14pt; mso-width-source: userset; mso-width-alt: 658" width=18><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 12pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=16 height=17>A</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 14pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=18>B</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>1</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>1</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>1</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Z</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>1</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">M</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>1</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">M</TD></TR></TBODY></TABLE>

So in the example above, if c5 was 7, i would expect the formula to give a value of 7 hours.