Using an Array formula with find text and find number start.

[gmittar]

Hi guys,

I'm working with a data set that I need to do some sumif type stuff with qualifications both on the rows and columns. This wonderful sight got me started with arrays, and I've gotten partway to my solution with an array. The below formula gets me part of the way, but now I'd like to add some qualifications to narrow the search.

First, for rows, I'd like to search for a word within the text, which is not always in the same spot. I got it working with Right(immediately after the sumif), but sometimes the qualifying text is somewhere in the middle.

Next, I'd like to add a Left search in addition to the above. I tried adding it after the Right function below, but it didn't work out.


SUM(IF(RIGHT('2017 Amt'!$A$7:$A$2000,6)=Data!$J229,IF(Data!$K$3='2017 Amt'!$C$6:$BL$6,'2017 Amt'!$C$7:$BL$2000)))

Thanks as always!

 

[Marcelo Branco]

A small data sample (~10 rows) along with criteria and expected result would be helpful.

M.

 

[gmittar]

How do I attach a file? I just see link options.

 

[gmittar]

Looks like I don't have attach permissions, pasting the image, and criteria are below.

Column (property) is selected based on a drop down list.

Multiple rows have to be summed for the property based on inconsistent labels. Revenue accounts always start with 3, so my plan is to write a statement that says, if the account starts with "3", and there's "food" in the label, sum the row for the selected property.

[TABLE="width: 940"]
<tbody>[TR]
[TD]Description[/TD]
[TD]Code[/TD]
[TD]Property 1[/TD]
[TD]Property 2[/TD]
[TD]Property 3[/TD]
[TD]Property 4[/TD]
[TD]Property 5[/TD]
[TD]Property 6[/TD]
[TD]Property 7[/TD]
[TD]Property 8[/TD]
[TD]Property 9[/TD]
[TD]Property 10[/TD]
[/TR]
[TR]
[TD]Rooms Revenue[/TD]
[TD="align: right"]300001[/TD]
[TD="align: right"]2700[/TD]
[TD="align: right"]2416[/TD]
[TD="align: right"]8391[/TD]
[TD="align: right"]4878[/TD]
[TD="align: right"]1974[/TD]
[TD="align: right"]229[/TD]
[TD="align: right"]5218[/TD]
[TD="align: right"]7988[/TD]
[TD="align: right"]4939[/TD]
[TD="align: right"]1461[/TD]
[/TR]
[TR]
[TD]Food Revenue bar[/TD]
[TD="align: right"]300002[/TD]
[TD="align: right"]9938[/TD]
[TD="align: right"]1299[/TD]
[TD="align: right"]5344[/TD]
[TD="align: right"]2326[/TD]
[TD="align: right"]5281[/TD]
[TD="align: right"]8741[/TD]
[TD="align: right"]8945[/TD]
[TD="align: right"]1658[/TD]
[TD="align: right"]9079[/TD]
[TD="align: right"]7777[/TD]
[/TR]
[TR]
[TD]Food Revenue rest[/TD]
[TD="align: right"]300021[/TD]
[TD="align: right"]9938[/TD]
[TD="align: right"]1299[/TD]
[TD="align: right"]5344[/TD]
[TD="align: right"]2326[/TD]
[TD="align: right"]5281[/TD]
[TD="align: right"]8741[/TD]
[TD="align: right"]8945[/TD]
[TD="align: right"]1658[/TD]
[TD="align: right"]9079[/TD]
[TD="align: right"]7777[/TD]
[/TR]
[TR]
[TD]Beverage Revenue[/TD]
[TD="align: right"]300003[/TD]
[TD="align: right"]7187[/TD]
[TD="align: right"]7339[/TD]
[TD="align: right"]5199[/TD]
[TD="align: right"]5536[/TD]
[TD="align: right"]2083[/TD]
[TD="align: right"]2663[/TD]
[TD="align: right"]5884[/TD]
[TD="align: right"]6392[/TD]
[TD="align: right"]5759[/TD]
[TD="align: right"]527[/TD]
[/TR]
[TR]
[TD]Misc Revenue[/TD]
[TD="align: right"]300004[/TD]
[TD="align: right"]6634[/TD]
[TD="align: right"]1116[/TD]
[TD="align: right"]6706[/TD]
[TD="align: right"]7253[/TD]
[TD="align: right"]4994[/TD]
[TD="align: right"]3062[/TD]
[TD="align: right"]5607[/TD]
[TD="align: right"]2302[/TD]
[TD="align: right"]5989[/TD]
[TD="align: right"]4390[/TD]
[/TR]
[TR]
[TD]Revenue from Food[/TD]
[TD="align: right"]300005[/TD]
[TD="align: right"]1196[/TD]
[TD="align: right"]2368[/TD]
[TD="align: right"]4553[/TD]
[TD="align: right"]8692[/TD]
[TD="align: right"]8462[/TD]
[TD="align: right"]9998[/TD]
[TD="align: right"]2957[/TD]
[TD="align: right"]7379[/TD]
[TD="align: right"]2050[/TD]
[TD="align: right"]1582[/TD]
[/TR]
[TR]
[TD]Revenue from Beverage[/TD]
[TD="align: right"]300006[/TD]
[TD="align: right"]3738[/TD]
[TD="align: right"]5794[/TD]
[TD="align: right"]7185[/TD]
[TD="align: right"]8056[/TD]
[TD="align: right"]4537[/TD]
[TD="align: right"]5553[/TD]
[TD="align: right"]8560[/TD]
[TD="align: right"]4028[/TD]
[TD="align: right"]6013[/TD]
[TD="align: right"]8986[/TD]
[/TR]
[TR]
[TD]Revenue from Rooms[/TD]
[TD="align: right"]300007[/TD]
[TD="align: right"]4328[/TD]
[TD="align: right"]3948[/TD]
[TD="align: right"]8109[/TD]
[TD="align: right"]6515[/TD]
[TD="align: right"]7700[/TD]
[TD="align: right"]9536[/TD]
[TD="align: right"]3837[/TD]
[TD="align: right"]2061[/TD]
[TD="align: right"]1906[/TD]
[TD="align: right"]353[/TD]
[/TR]
[TR]
[TD]Rooms expense[/TD]
[TD="align: right"]400000[/TD]
[TD="align: right"]7039[/TD]
[TD="align: right"]2372[/TD]
[TD="align: right"]6691[/TD]
[TD="align: right"]9508[/TD]
[TD="align: right"]3297[/TD]
[TD="align: right"]3443[/TD]
[TD="align: right"]249[/TD]
[TD="align: right"]6733[/TD]
[TD="align: right"]3757[/TD]
[TD="align: right"]5852[/TD]
[/TR]
[TR]
[TD]Food expense[/TD]
[TD="align: right"]500000[/TD]
[TD="align: right"]5980[/TD]
[TD="align: right"]453[/TD]
[TD="align: right"]5819[/TD]
[TD="align: right"]6273[/TD]
[TD="align: right"]2058[/TD]
[TD="align: right"]2485[/TD]
[TD="align: right"]4248[/TD]
[TD="align: right"]9491[/TD]
[TD="align: right"]9603[/TD]
[TD="align: right"]2711[/TD]
[/TR]
[TR]
[TD]Beverage expense[/TD]
[TD="align: right"]600000[/TD]
[TD="align: right"]2118[/TD]
[TD="align: right"]4012[/TD]
[TD="align: right"]395[/TD]
[TD="align: right"]9282[/TD]
[TD="align: right"]9548[/TD]
[TD="align: right"]8872[/TD]
[TD="align: right"]3239[/TD]
[TD="align: right"]6653[/TD]
[TD="align: right"]1112[/TD]
[TD="align: right"]7461[/TD]
[/TR]
[TR]
[TD]Misc expense[/TD]
[TD="align: right"]700000[/TD]
[TD="align: right"]6292[/TD]
[TD="align: right"]2052[/TD]
[TD="align: right"]2919[/TD]
[TD="align: right"]7473[/TD]
[TD="align: right"]2762[/TD]
[TD="align: right"]2391[/TD]
[TD="align: right"]143[/TD]
[TD="align: right"]6312[/TD]
[TD="align: right"]1270[/TD]
[TD="align: right"]9224[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Property #[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Food Revenue[/TD]
[TD="align: right"]15241[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Assuming your data sample in A1:L13, headers in row 1

Try
=SUM(IF(0+LEFT(B2:B13)=3,IF(ISNUMBER(SEARCH("Food",A2:A13)),IF(C1:L1="Property "&B17,C2:L13))))
Ctrl+Shift+Enter

Where B17 contains 3 (Property #)

Hope this helps

M.

 

[gmittar]

Thanks Marcelo,

Unfortunately that didn't work for me. I thought it might be the Left function was missing a place identifier, but I added that to no effect. I assume a blank identifier on Left assumes 1?

The formula is returning #value ! If I remove the 0+ from the Left function, the entire thing returns zero value.

I should also note that the property reference is a straight cell reference, I think I was unclear on that originally.

 

[Marcelo Branco]

Have you confirmed the formula with Ctrl+Shift+Enter, not just Enter?

It worked perfectly for me.


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][td="bgcolor: #DCE6F1"]

H​

[/td][td="bgcolor: #DCE6F1"]

I​

[/td][td="bgcolor: #DCE6F1"]

J​

[/td][td="bgcolor: #DCE6F1"]

K​

[/td][td="bgcolor: #DCE6F1"]

L​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Description​

[/td][td]

Code​

[/td][td]

Property 1​

[/td][td]

Property 2​

[/td][td]

Property 3​

[/td][td]

Property 4​

[/td][td]

Property 5​

[/td][td]

Property 6​

[/td][td]

Property 7​

[/td][td]

Property 8​

[/td][td]

Property 9​

[/td][td]

Property 10​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Rooms Revenue​

[/td][td]

300001​

[/td][td]

2700​

[/td][td]

2416​

[/td][td]

8391​

[/td][td]

4878​

[/td][td]

1974​

[/td][td]

229​

[/td][td]

5218​

[/td][td]

7988​

[/td][td]

4939​

[/td][td]

1461​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Food Revenue bar​

[/td][td]

300002​

[/td][td]

9938​

[/td][td]

1299​

[/td][td="bgcolor:#D9D9D9"]

5344​

[/td][td]

2326​

[/td][td]

5281​

[/td][td]

8741​

[/td][td]

8945​

[/td][td]

1658​

[/td][td]

9079​

[/td][td]

7777​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Food Revenue rest​

[/td][td]

300021​

[/td][td]

9938​

[/td][td]

1299​

[/td][td="bgcolor:#D9D9D9"]

5344​

[/td][td]

2326​

[/td][td]

5281​

[/td][td]

8741​

[/td][td]

8945​

[/td][td]

1658​

[/td][td]

9079​

[/td][td]

7777​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

Beverage Revenue​

[/td][td]

300003​

[/td][td]

7187​

[/td][td]

7339​

[/td][td]

5199​

[/td][td]

5536​

[/td][td]

2083​

[/td][td]

2663​

[/td][td]

5884​

[/td][td]

6392​

[/td][td]

5759​

[/td][td]

527​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

Misc Revenue​

[/td][td]

300004​

[/td][td]

6634​

[/td][td]

1116​

[/td][td]

6706​

[/td][td]

7253​

[/td][td]

4994​

[/td][td]

3062​

[/td][td]

5607​

[/td][td]

2302​

[/td][td]

5989​

[/td][td]

4390​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

Revenue from Food​

[/td][td]

300005​

[/td][td]

1196​

[/td][td]

2368​

[/td][td="bgcolor:#D9D9D9"]

4553​

[/td][td]

8692​

[/td][td]

8462​

[/td][td]

9998​

[/td][td]

2957​

[/td][td]

7379​

[/td][td]

2050​

[/td][td]

1582​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

Revenue from Beverage​

[/td][td]

300006​

[/td][td]

3738​

[/td][td]

5794​

[/td][td]

7185​

[/td][td]

8056​

[/td][td]

4537​

[/td][td]

5553​

[/td][td]

8560​

[/td][td]

4028​

[/td][td]

6013​

[/td][td]

8986​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

Revenue from Rooms​

[/td][td]

300007​

[/td][td]

4328​

[/td][td]

3948​

[/td][td]

8109​

[/td][td]

6515​

[/td][td]

7700​

[/td][td]

9536​

[/td][td]

3837​

[/td][td]

2061​

[/td][td]

1906​

[/td][td]

353​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

Rooms expense​

[/td][td]

400000​

[/td][td]

7039​

[/td][td]

2372​

[/td][td]

6691​

[/td][td]

9508​

[/td][td]

3297​

[/td][td]

3443​

[/td][td]

249​

[/td][td]

6733​

[/td][td]

3757​

[/td][td]

5852​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td]

Food expense​

[/td][td]

500000​

[/td][td]

5980​

[/td][td]

453​

[/td][td]

5819​

[/td][td]

6273​

[/td][td]

2058​

[/td][td]

2485​

[/td][td]

4248​

[/td][td]

9491​

[/td][td]

9603​

[/td][td]

2711​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

12​

[/td][td]

Beverage expense​

[/td][td]

600000​

[/td][td]

2118​

[/td][td]

4012​

[/td][td]

395​

[/td][td]

9282​

[/td][td]

9548​

[/td][td]

8872​

[/td][td]

3239​

[/td][td]

6653​

[/td][td]

1112​

[/td][td]

7461​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

13​

[/td][td]

Misc expense​

[/td][td]

700000​

[/td][td]

6292​

[/td][td]

2052​

[/td][td]

2919​

[/td][td]

7473​

[/td][td]

2762​

[/td][td]

2391​

[/td][td]

143​

[/td][td]

6312​

[/td][td]

1270​

[/td][td]

9224​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

14​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

15​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

16​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

17​

[/td][td]

Property #​

[/td][td]

3​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

18​

[/td][td]

Total Food Revenue​

[/td][td]

15241​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Formula in B18
=SUM(IF(0+LEFT(B2:B13)=3,IF(ISNUMBER(SEARCH("Food",A2:A13)),IF(C1:L1="Property "&B17,C2:L13))))
Ctrl+Shift+Enter

M.

 

[gmittar]

Yeah, I'm definitely doing the CSE array command. In removing different parts of the formula, it appears to be the Left function that's breaking it. Of course, without that function, the result is worthless.

Here's the actual formula, can you spot any errors? "J229" is the "food" cell. I tried it with the actual word in the cell and it didn't change the result.

SUM(IF(0+LEFT('2017 Amt'!$B$7:$B$2000)=3,IF(ISNUMBER(SEARCH(J229,'2017 Amt'!A7:A2000)),IF('2017 Amt'!$C$6:$BL$6=Data!$K$3,'2017 Amt'!$C$7:$BL$2000))))

 

[Marcelo Branco]

Try

=SUM(IF(LEFT('2017 Amt'!$B$7:$B$2000)="3",IF(ISNUMBER(SEARCH(J229,'2017 Amt'!A7:A2000)),IF('2017 Amt'!$C$6:$BL$6=Data!$K$3,'2017 Amt'!$C$7:$BL$2000))))
Ctrl+Shift+Enter

M.

 

[gmittar]

Good morning, that did the trick! Thanks so much!