Sumproduct mid find

[koishi]

Hi,

I am trying to make a simple formula to calculate values in an array of cells. The cells contain text and numbers, text or can be blank. I am not an excel professional...

Data sample:
Cell Cell-content
B2 Mr. Hansen Dlx Dbl /55
B3 Mr. Olsen Sup Sgl /45
B4 Mrs. Simpson Std Sgl /40
B5 Robert Smith TWN /43.25
B6
B7 Maria M Sgl /45
B8 Fred Olsen

I have made one formula that will work as long as the data in the cells are valid with a / following with a number:

=SUMPRODUCT(MID(B2:B5,FIND("/",B2:B5)+1,10)+0)

How can I make it work with cells that do not return valid data?

Any help much appreciated

Best regards
Ken

 

[Scott Huish]

=sumproduct(--(replace(b2:b8,1,find("/",b2:b8&"/"),"0")))

 

[Scott Huish]

Excel Workbook
	A	B	C	D	E
1
2		Mr. Hansen Dlx Dbl /55			228.25
3		Mr. Olsen Sup Sgl /45
4		Mrs. Simpson Std Sgl /40
5		Robert Smith TWN /43.25
6
7		Maria M Sgl /45
8		Fred Olsen
Sheet1

 

[koishi]

Dear Hot Pepper,

Thank you for your quick and perfect working reply!

Best regards
Ken

 

[Scott Huish]

You're welcome.

 

[koishi]

I have one follow up question. How could I make a test to check if /xx is occurring in a range of cells and if the values xx are within a certain range?

Any help very much appreciated.

Excel Workbook
	A	B	C	D
1	Cell	Friday 22.4.2011	Saturday 23.04.2011	Sunday 24.04.2011
2	B2	Mr. Hansen Dlx Dbl /55	Mr. Hansen Dlx Dbl /55
3	B3	Mr. Olsen Sup Sgl /45	Mr. Olsen Sup Sgl /45
4	B4	Mrs. Simpson Std Sgl /40
5	B5	Robert Smith TWN /43.25	Robert Smith TWN /43.25	Robert Smith TWN /43.25
6	B6		Service
7	B7	Maria M Sgl /45	Maria M Sgl /45	Maria M Sgl /45
8	B8	Fred Olsen	Fred Olsen /45	Fred Olsen /45
9
10	Total income	228.25	233.25	133.25
11	Average Rate	38.04	46.65	44.42
12	Error check	Check if any cell includes text without "/number between 10-90"
13	Could return a red cell (with the number of occurances or even which cells)?
14
15	Occupied rooms	6	5	3
16	Available rooms	1	1	4
17	Occupancy rate	75%	63%	38%
18
19	In house
20	Checked out
21	Checked in
File

 

[Peter_SSs]

Welcome to the MrExcel board!

If I have understood correctly, you could use Conditional Formatting to highlight any problem cells. I selected B2:D8 and then applied the Conditional Formatting shown.

If you need more help with how to set that up post back and confirm which version of Excel you are using.

Also note that with Excel jeanie you can use the ‘Analyse range (Forum)’ field near the top left of the Excel jeanie screen to restrict the number of formulas generated. In that field, you can use Ctrl+Click/Drag to select multiple disjoint ranges if required. There is generally no need to display multiple formulas that are basically the same, it just fills up the board.

Excel Workbook
	B	C	D
1	Friday 22.4.2011	Saturday 23.04.2011	Sunday 24.04.2011
2	Mr. Hansen Dlx Dbl /55	Mr. Hansen Dlx Dbl /55
3	Mr. Olsen Sup Sgl /45	Mr. Olsen Sup Sgl /45
4	Mrs. Simpson Std Sgl /40
5	Robert Smith TWN /43.25	Robert Smith TWN /43.25	Robert Smith TWN /43.25
6		Service
7	Maria M Sgl /45	Maria M Sgl /45	Maria M Sgl /145
8	Fred Olsen	Fred Olsen /45	Fred Olsen /45
9
File

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2	1. / Formula is =AND(B2<>"",B2<>"Service",OR(REPLACE(B2,1,FIND("/",B2&"/"),0)+0<10,REPLACE(B2,1,FIND("/",B2&"/"),0)+0>90))	Abc

 

[koishi]

Dear Peter,

Thank you for the quick response. I haven't had time yet to try to understand how it works, but it does work very well for what my intention was.

Thanks for the info about Jeanie also.

Best regards
Ken

 

[koishi]

I hope I am not misusing this forum, but the first responses I got was so good that they got me very excited about possibilities in Excel and in solving and improving what i am working on.

Thus I have one follow up query;

How can I find:

1. Occupancy per room for day of the week(Monday to Sunday), and a summary of occupancy per room for the month

(displayed in a separate sheet)

2. Occupancy per room type per month

(displayed in a separate sheet)

Room standard Room type
STD SGL
SUP TWN
DLX DBL
TPL

I forgot, I should probably have added the result:

<table style="padding-right: 2pt; padding-left: 2pt; font-size: 11pt; font-family: Calibri,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td>
</td> <td>A</td> <td>B</td> <td>C</td> <td>D</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td> <td style="font-size: 8pt; font-family: Arial; background-color: rgb(192, 192, 192); text-align: center;">ROOM</td> <td style="font-size: 10pt; font-family: Arial; background-color: rgb(153, 204, 255);">Friday 22.4.2011</td> <td style="font-size: 10pt; font-family: Arial; background-color: rgb(153, 204, 255);">Saturday 23.04.2011</td> <td style="font-size: 10pt; font-family: Arial; background-color: rgb(153, 204, 255);">Sunday 24.04.2011</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td> <td style="font-size: 8pt; font-family: Arial; background-color: rgb(192, 192, 192); text-align: left;">Room 001 SUP TWN</td> <td style="font-size: 10pt; font-family: Arial; background-color: rgb(255, 255, 0);">Mr. Hansen Dlx Dbl %55</td> <td style="font-size: 10pt; font-family: Arial; background-color: rgb(255, 0, 0);">Mr. Hansen Dlx Dbl %55</td> <td style="font-size: 10pt; font-family: Arial;"> </td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td> <td style="font-size: 8pt; font-family: Arial; background-color: rgb(192, 192, 192); text-align: left;">Room 002 SUP DBL</td> <td style="font-size: 10pt; font-family: Arial; background-color: rgb(255, 255, 0);">Mr. Olsen Sup Sgl %45</td> <td style="font-size: 10pt; font-family: Arial; background-color: rgb(255, 0, 0);">Mr. Olsen Sup Sgl %45</td> <td style="font-size: 10pt; font-family: Arial;"> </td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td> <td style="font-size: 8pt; font-family: Arial; background-color: rgb(192, 192, 192); text-align: left;">Room 101 STD TWN</td> <td style="font-size: 10pt; font-family: Arial; background-color: rgb(255, 0, 0);">Mrs. Simpson Std Sgl %40</td> <td style="font-size: 10pt; font-family: Arial;"> </td> <td style="font-size: 10pt; font-family: Arial;"> </td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td> <td style="font-size: 8pt; font-family: Arial; background-color: rgb(192, 192, 192); text-align: left;">Room 102 DLX TWN</td> <td style="font-size: 10pt; font-family: Arial; background-color: rgb(0, 255, 0);">Robert Smith TWN %43.25</td> <td style="font-size: 10pt; font-family: Arial; background-color: rgb(255, 255, 0);">Robert Smith TWN %43.25</td> <td style="font-size: 10pt; font-family: Arial; background-color: rgb(255, 255, 0);">Robert Smith TWN %43.25</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td> <td style="font-size: 8pt; font-family: Arial; background-color: rgb(192, 192, 192); text-align: left;">Room 201 DLX DBL</td> <td style="font-size: 10pt; font-family: Arial;"> </td> <td style="font-size: 10pt; font-family: Arial; background-color: rgb(242, 42, 204);">Service</td> <td style="font-size: 10pt; font-family: Arial;"> </td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td> <td style="font-size: 8pt; font-family: Arial; background-color: rgb(192, 192, 192); text-align: left;">Room 202 SUP DBL</td> <td style="font-size: 10pt; font-family: Arial; background-color: rgb(255, 255, 0);">Maria M Sgl %45</td> <td style="font-size: 10pt; font-family: Arial; background-color: rgb(255, 255, 0);">Maria M Sgl %45</td> <td style="font-size: 10pt; font-family: Arial; background-color: rgb(255, 0, 0);">Maria M Sgl %45</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td> <td style="font-size: 8pt; font-family: Arial; background-color: rgb(192, 192, 192); text-align: left;">Room 301 STD DBL</td> <td style="font-size: 10pt; font-family: Arial; background-color: rgb(255, 255, 0);">Fred Olsen</td> <td style="font-size: 10pt; font-family: Arial; background-color: rgb(255, 255, 0);">Fred Olsen %45</td> <td style="font-size: 10pt; font-family: Arial; background-color: rgb(255, 255, 0);">Fred Olsen %45</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td> <td>Total income</td> <td style="text-align: right;">228.25</td> <td style="text-align: right;">233.25</td> <td style="text-align: right;">133.25</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td> <td>Average Rate</td> <td style="text-align: right;">38.04</td> <td style="text-align: right;">46.65</td> <td style="text-align: right;">44.42</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">12</td> <td> </td> <td style="font-size: 10pt; font-family: Arial;"> </td> <td> </td> <td> </td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">13</td> <td>Occupied rooms</td> <td style="text-align: right;">6</td> <td style="text-align: right;">5</td> <td style="text-align: right;">3</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">14</td> <td>Available rooms</td> <td style="text-align: right;">1</td> <td style="text-align: right;">1</td> <td style="text-align: right;">4</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">15</td> <td>Occupancy rate</td> <td style="text-align: right;">75%</td> <td style="text-align: right;">63%</td> <td style="text-align: right;">38%</td></tr></tbody></table>
<table style="font-size: 10pt; border-color: rgb(0, 255, 0); color: rgb(0, 0, 0); border-style: groove; font-family: Arial; background-color: rgb(255, 252, 249);"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="font-size: 9pt; font-family: Arial;" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="font-size: 10pt; background-color: rgb(202, 202, 202);"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>B10</td> <td>=SUMPRODUCT(--(REPLACE(B1:B8,1,FIND("%",B1:B8&"%"),"0")))</td></tr> <tr> <td>B13</td> <td>=7-COUNTBLANK(B2:B8)-COUNTIF(B2:B8,"Service")</td></tr> <tr> <td>B14</td> <td>=(7-COUNTIF(B2:B8,"Service"))-B13</td></tr></tbody></table></td></tr></tbody></table>
<table style="font-size: 10pt; border-color: rgb(255, 0, 0); border-style: groove; font-family: Arial; background-color: rgb(255, 252, 249);"> <tbody> <tr> <td>Conditional formatting </td></tr> <tr> <td> <table style="padding-right: 2pt; padding-left: 2pt; font-size: 10pt; font-family: Arial;" border="1" cellpadding="0" cellspacing="0"> <tbody> <tr> <td>Cell</td> <td>Nr.: / Condition</td> <td>Format</td></tr> <tr> <td>B8</td> <td>1. / Formula is =AND(B2<>"",B2<>"Service",OR(REPLACE(B2,1,FIND("%",B2&"%"),0)+0<10,REPLACE(B2,1,FIND("%",B2&"%"),0)+0>90))</td> <td style="background-color: rgb(0, 0, 0);">Abc</td></tr></tbody></table></td></tr></tbody></table>

Best regards
Ken

 

[Peter_SSs]

I hope I am not misusing this forum

You are not - it is here for questions.

How can I find:

1. Occupancy per room for day of the week(Monday to Sunday), and a summary of occupancy per room for the month

For the day, what is wrong with how you were doing it in post #6? What results do you expect and why?

For the month, hard to know if I can help without knowing the sheet layout for the month (or months if more than one month on the sheet). We have only seen 3 days worth so don't know how the rest is laid out.

Similar difficulty with your other question.