=SUM Formula (Word Search)

[bobzy20]

Hi,

This will probably be a very simple fix but with limited knowledge of how these formulas work, it isn't easy for me!

I'm using the following formula below and all I want to do is add an additional search word: Q.E.F

=SUM(LEN(Purchases!B:B)-LEN(SUBSTITUTE(Purchases!B:B,"QEF","")))/LEN("QEF")

Any help would be great.

Thanks

 

[RobP]

would you be able to post any sample data you have ?

(and maybe explain what you are trying to do with it a little more please ?)

Rob

 

[Fluff]

How about

=SUM(LEN(SUBSTITUTE(Purchases!B:B,".",""))-LEN(SUBSTITUTE(SUBSTITUTE(Purchases!B:B,".",""),"QEF","")))/LEN("QEF")

I would also advise against using whole column references, as it can slow things down.

 

[bobzy20]

Thanks, that worked lovely.

Have a nice weekend.

 

[Fluff]

You're welcome & thanks for the feedback

 

[bobzy20]

Hi,

Following on from the above, I have now added a new column (££) where I would like to create a formula to calculate the total value for each Name. I will explain below.

Column No. uses the following formula to look at column Name 1 and calculate the number of times it finds the associated word found in the Name 2 column.

=SUM(LEN(A:A)-LEN(SUBSTITUTE(A:A,"PAH","")))/LEN("PAH")

Note: The search word PAH will change to the next word in the Name 2 column as you run down the list.

I'm now looking to calculate the total value associated from column £ with the number found in the No. column. I have manually added them up in the ££ column just to show you.

Hopefully you understand what I have tried to communicate above.

Name 1	£		Name 2	No.	££
24 08 Apr 24 CF	£6.00		PAH	3	£19.50
24 08 Apr 24 CF	£2.00		CF	2	£8.00
24 12 Apr 24 BHF	£17.50		WSB	7	£22.60
24 12 Apr 24 WSB	£2.00		BAR	0	£12.00
24 14 Apr 24 PAH	£10.00		VF	0
24 16 Apr 24 WSB	£4.10		QEF	5	£42.50
24 17 Apr 24 WSB	£4.10		BHF	0	£17.50
24 18 Apr 24 WSB	£4.10		BC	0	£20.00
24 19 Apr 24 PAH	£6.00		OX	0
24 19 Apr 24 QEF	£6.00		SS	0
24 19 Apr 24 Q.E.F	£4.00		SR	0
24 19 Apr 24 Q.E.F	£7.50		SA	0
24 19 Apr 24 QEF	£10.00
24 19 Apr 24 QEF	£15.00
24 19 Apr 24 WSB	£2.10
24 21 Apr 24 PAH	£3.50
24 23 Apr 24 BAR	£12.00
24 25 Apr 24 WSB	£2.10
24 25 Apr 24 WSB	£4.10
24 30 Apr 24 BC	£20.00

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F
2	Name 1	£		Name 2	No.	££
3	24 08 Apr 24 CF	£6.00		PAH	3	19.5
4	24 08 Apr 24 CF	£2.00		CF	2	8
5	24 12 Apr 24 BHF	£17.50		WSB	7	22.6
6	24 12 Apr 24 WSB	£2.00		BAR	1	12
7	24 14 Apr 24 PAH	£10.00		VF	0	0
8	24 16 Apr 24 WSB	£4.10		QEF	5	42.5
9	24 17 Apr 24 WSB	£4.10		BHF	1	17.5
10	24 18 Apr 24 WSB	£4.10		BC	1	20
11	24 19 Apr 24 PAH	£6.00		OX	0	0
12	24 19 Apr 24 QEF	£6.00		SS	0	0
13	24 19 Apr 24 Q.E.F	£4.00		SR	0	0
14	24 19 Apr 24 Q.E.F	£7.50		SA	0	0
15	24 19 Apr 24 QEF	£10.00
16	24 19 Apr 24 QEF	£15.00
17	24 19 Apr 24 WSB	£2.10
18	24 21 Apr 24 PAH	£3.50
19	24 23 Apr 24 BAR	£12.00
20	24 25 Apr 24 WSB	£2.10
21	24 25 Apr 24 WSB	£4.10
22	24 30 Apr 24 BC	£20.00
Sheet5

Cell Formulas
Range	Formula
E3:E7,E9:E14	E3	=COUNTIFS(A:A,"*"&D3)
F3:F7,F9:F14	F3	=SUMIFS(B:B,A:A,"*"&D3)
E8	E8	=SUM(COUNTIFS(A:A,{"*QEF","*Q.E.F"}))
F8	F8	=SUM(SUMIFS(B:B,A:A,{"*QEF","*Q.E.F"}))

 

[bobzy20]

Hi,

Thanks very much that works lovely in the example presented above but not in my spreadsheet because I didn't give you all the information.

My bad, sorry.

When it looks for the words they are amongst other words in a cell, I will give an example below. When its looking for the word PAH, its in the middle of what you see below. Can it possibly be tweaked to search like that?

24 14 Apr 24 PAH THE WALK

Sorry my friend

 

[Fluff]

For both the countifs & sumifs, you just need to add another * to the end like

=COUNTIFS(A:A,"*"&D3&"*")

 

[bobzy20]

Thanks,

That works really well now apart from the QEF part, does this formula need tweaking, as I know its works sightly different to the others. I'm not sure how to apply the amendment to this part.

Thanks