SUMIF Criteria
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 ? |
You could use the SUMPRODUCT function instead. For example:
=SUMPRODUCT((LEFT(B2:B100,2)="32")*(B2:B100)) Brent Martin wrote: 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 ? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
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 -----Original Message----- You could use the SUMPRODUCT function instead. For example: =SUMPRODUCT((LEFT(B2:B100,2)="32")*(B2:B100)) Brent Martin wrote: 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 ? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html . |
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. Brent wrote: 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 -----Original Message----- You could use the SUMPRODUCT function instead. For example: =SUMPRODUCT((LEFT(B2:B100,2)="32")*(B2:B100)) Brent Martin wrote: 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 ? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html . -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Could the format for the spreadsheet that works be different - text instead
of numeric? "Brent Martin" wrote: 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 ? |
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 -----Original Message----- Could the format for the spreadsheet that works be different - text instead of numeric? "Brent Martin" wrote: 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 ? . |
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 "Brent" wrote: 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 -----Original Message----- Could the format for the spreadsheet that works be different - text instead of numeric? "Brent Martin" wrote: 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 ? . |
All times are GMT +1. The time now is 01:54 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com