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.

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

<tbody>
</tbody>

 

[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.

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

1​

Description​

Code​

Property 1​

Property 2​

Property 3​

Property 4​

Property 5​

Property 6​

Property 7​

Property 8​

Property 9​

Property 10​

2​

Rooms Revenue​

300001​

2700​

2416​

8391​

4878​

1974​

229​

5218​

7988​

4939​

1461​

3​

Food Revenue bar​

300002​

9938​

1299​

5344​

2326​

5281​

8741​

8945​

1658​

9079​

7777​

4​

Food Revenue rest​

300021​

9938​

1299​

5344​

2326​

5281​

8741​

8945​

1658​

9079​

7777​

5​

Beverage Revenue​

300003​

7187​

7339​

5199​

5536​

2083​

2663​

5884​

6392​

5759​

527​

6​

Misc Revenue​

300004​

6634​

1116​

6706​

7253​

4994​

3062​

5607​

2302​

5989​

4390​

7​

Revenue from Food​

300005​

1196​

2368​

4553​

8692​

8462​

9998​

2957​

7379​

2050​

1582​

8​

Revenue from Beverage​

300006​

3738​

5794​

7185​

8056​

4537​

5553​

8560​

4028​

6013​

8986​

9​

Revenue from Rooms​

300007​

4328​

3948​

8109​

6515​

7700​

9536​

3837​

2061​

1906​

353​

10​

Rooms expense​

400000​

7039​

2372​

6691​

9508​

3297​

3443​

249​

6733​

3757​

5852​

11​

Food expense​

500000​

5980​

453​

5819​

6273​

2058​

2485​

4248​

9491​

9603​

2711​

12​

Beverage expense​

600000​

2118​

4012​

395​

9282​

9548​

8872​

3239​

6653​

1112​

7461​

13​

Misc expense​

700000​

6292​

2052​

2919​

7473​

2762​

2391​

143​

6312​

1270​

9224​

14​

15​

16​

17​

Property #​

3​

18​

Total Food Revenue​

15241​

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!