Sumif using wildcards & number range

[ajfaber]

Hello everyone,

I did some searching through the discussion boards and I wanted to be sure I understood my apparent dilemma: Wildcards cannot be used to search a number range..... ?

Example(Input! Wkst):

Job#-------Desc---------Lumber used
501--------House--------1520
502--------Farm----------25
502.2------Farm-field-----600
502.3------Farm-house---300

-On a separate worksheet I am importing the information but consolidating the sum of the lumber amounts into one job#.

Example(Wkst1):

Job#-------Desc---------Lumber used
501--------House--------1520
502--------Farm----------925

I was trying to use =SUMIF(Input!$A$2:$A$5,"*"&A1&"*",Input!$C$2:$C$5)

The 'function arguement' window shows that it recognizes what I am trying to do, and that I have all the arguements right, but the solution returns an answer of 0 no matter what the answer might really be.

Please let me know if I am understanding this correctly so I don't spend more time trying to make it work, and if you have a better solution, I would love your advice.

Thanks in advance!

 

[T. Valko]

Hello everyone,

I did some searching through the discussion boards and I wanted to be sure I understood my apparent dilemma: Wildcards cannot be used to search a number range..... ?

Example(Input! Wkst):

Job#-------Desc---------Lumber used
501--------House--------1520
502--------Farm----------25
502.2------Farm-field-----600
502.3------Farm-house---300

-On a separate worksheet I am importing the information but consolidating the sum of the lumber amounts into one job#.

Example(Wkst1):

Job#-------Desc---------Lumber used
501--------House--------1520
502--------Farm----------925

I was trying to use =SUMIF(Input!$A$2:$A$5,"*"&A1&"*",Input!$C$2:$C$5)

The 'function arguement' window shows that it recognizes what I am trying to do, and that I have all the arguements right, but the solution returns an answer of 0 no matter what the answer might really be.

Please let me know if I am understanding this correctly so I don't spend more time trying to make it work, and if you have a better solution, I would love your advice.

Thanks in advance!

Wildcards don't work on numbers.

Try it like this...

=SUMPRODUCT(--(INT(Input!$A$2:$A$5)=A1),Input!$C$2:$C$5)

 

[scottylad2]

slightly diffeent

Excel Workbook
	E	F	G	H
13	501	1520	501	1520
14	502	25	502	925
15	502.2	600
16	502.3	300
Sheet1

 

[ajfaber]

Thanks T. Valco! It appears that your formula works great. Saves me having to put a silly " ' " in front of my numbers when I 10-key.

 

[T. Valko]

Thanks T. Valco! It appears that your formula works great. Saves me having to put a silly " ' " in front of my numbers when I 10-key.

You're welcome. Thanks for the feedback!