SUMIF Criteria

B

Brent Martin

In the criteria section of SUMIF function I cannot get
the wildcard function to work. "32*"
It works fine for me on another computer, is there some
setting or something that I should check for on this
computer to make it work ?
 
D

Debra Dalgleish

You could use the SUMPRODUCT function instead. For example:

=SUMPRODUCT((LEFT(B2:B100,2)="32")*(B2:B100))
 
B

Brent

Thanks, I can get the SUMPRODUCT to work, just seems more
complex then the
SUMIF function.
Can't figure out why this =SUMIF(DEC!
$D$3:$D$320,"32*",DEC!H$3:H$320) won't
work.
I get zero for the answer, but I use this formula on
another spreadsheet on
another computer and it works just fine.

Brent
 
D

Debra Dalgleish

You can use the wildcard character with text in a SUMIF formula, e.g.

=SUMIF(DEC!$D$3:$D$320,"AB*",DEC!H$3:H$320)

but the wildcard character won't work with numbers.
 
G

Gary Rowe

Could the format for the spreadsheet that works be different - text instead
of numeric?
 
B

Brent

Well, I did check the cells format in the spreadsheet
that does not work to make sure/change to numeric. That
still did not do anything. Did not check the overall
format of the sheet.

Thanks,

Brent
 
G

Gary Rowe

Brent,
I set up a spreadsheet with a range of numbers entered as text. You need to
format the cells as text prior to entering the numbers. I set up another
range with numbers to add based on the criteria. The formula
(=SUMIF(D7:D29,"*40",H7:H29) works. You can test the cells in your criteria
range by using the formula =istext(cell) and if it is it will result in True.
Gary
 
Top