help SUMIF function

[riiiiiichhhhhhhhh]

Hi guys,
can please help me figure out how do i sum if cells in a range contain a certain number regardless whether they also contain a letter?

1002A	100	sum numbers in column B for all cells in column A that contain the number 1002​
1002B	200

 

[Automatrix]

Can column A contain numbers longer than 4 digits, or more than one letter, and is the letter always at the end of the string?

 

[riiiiiichhhhhhhhh]

Can column A contain numbers longer than 4 digits, or more than one letter, and is the letter always at the end of the string?

can contain numbers longer than 4 digits, not more than one letter, letter always at the end

 

[Leo Skywalker]

Hi there; try this:

=SUMPRODUCT(ISNUMBER(FIND("1002",A2:A100))*(B2:B100))

 

[Peter_SSs]

From your descriptions, this may also work for you

=SUM(FILTER(B2:B100,LEFT(A2:A100,4)="1002",0))

 

[riiiiiichhhhhhhhh]

From your descriptions, this may also work for you

=SUM(FILTER(B2:B100,LEFT(A2:A100,4)="1002",0))

THANKS!
can i add another criteria? would like to the same thing but add a text for each row in a different column

1002A	100	ACTIVE	sum all cells in column A that contain a letter only if column C contains "active"
1002B	200	CLOSED

 

[Peter_SSs]

can i add another criteria? ... sum all cells in column A that contain a letter only if column C contains "active"

That is not just adding a criteria since you are also changing the initial criteria from
cells in column A that contain the number 1002
to
cells in column A that contain a letter

Anyway, see if this is what you want

22 10 15.xlsm
	A	B	C	D	E
1
2	1002	1	ACTIVE		8
3	1002A	2	CLOSED
4	1002B	3	ACTIVE
5	1003	4	CLOSED
6	2222X	5	ACTIVE
7
Sum (2)

Cell Formulas
Range	Formula
E2	E2	=SUM(FILTER(B2:B100,NOT(ISNUMBER(A2:A100))*(C2:C100="ACTIVE"),0))

 

[Leo Skywalker]

THANKS!
can i add another criteria? would like to the same thing but add a text for each row in a different column

1002A	100	ACTIVE	sum all cells in column A that contain a letter only if column C contains "active"
1002B	200	CLOSED

You can also try this:

=SUMPRODUCT((B2:B100)*ISNUMBER(FIND("1002",A2:A100))*(C2:C100="active"))