countifs

[Mattlake]

Hi

I hope someone is going to have a simple answer for a countifs query i have need resolving.

I have a long spreadsheet (1400 lines) which someone has 'been using' badly, and i need to count if certain words are used in a column.

i need to count light fittings unfortunately the person who entered it all in put model numbers (all in column E) in that no longer exist so i need to count them if the field has 600x600 and 40w as well as a having an x in column H

I have got =COUNTIFS(Sheet1!E:E,B3,Sheet1!H:H,"X") (B3 has the text of 600x600 1 x 40w EBMCFP140Z) to work, but with this would need copy every different entry in to a single column

does this make sense and if so (phew) is there a way?

Thank you for saving the headache

Regards
Matthew

 

[Fluff]

Try putting this into B3 600x600*40w* and use your existing formula

 

[AlanY]

try this

Book1
	A	B	C	D	E	F	G	H	I	J
1
2
3					600x600 1 x 40w			X		4
4
5
6
7
8
9
10
11					600x600 1 x 40w			X
12					600x600 1 x 40w			X
13					600x600 1 x 30w
14					600x600 1 x 40w
15
16
17
18
19
20
21
22
23
24					500x600 1 x 40w			X
25					600x600 1 x 40w			X
26					600x600 1 x 30w			X
27					600x600 1 x 50w			X
28
29
Sheet1

Cell Formulas
Range	Formula
J3	J3	=SUMPRODUCT(--(ISNUMBER(SEARCH("600x600",E1:E27))),--(ISNUMBER(SEARCH("40w",E1:E27))),--(H1:H27="X"))

 

[JamesCanale]

MrExcelPlayground20.xlsx
	B	C	D	E	F	G	H
1	600x600	2		Thing			Other
2	40w			600x600 jamie 40w			x
3				600x600 fred			x
4				600x600 40w harry
5				40w jamie			x
6				40w John 600x600			x
Sheet16

Cell Formulas
Range	Formula
C1	C1	=SUMPRODUCT(--ISNUMBER(SEARCH(B1,E:E)),--ISNUMBER(SEARCH(B2,E:E)),--(H:H="x"))

 

[Mattlake]

Thanks for this
I have used the sum =COUNTIFS(Sheet1!E:E,B3,Sheet1!H:H,"X") which works

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Mattlake]

Thanks for this
I have used the sum =COUNTIFS(Sheet1!E:E,B3,Sheet1!H:H,"X") and entered 600x600*40w* into B3 which works (sorry forgot to put the bit in that was edited)