sumif wildcard for text and numbers

  • Thread starter Thread starter kijijijt
  • Start date Start date
K

kijijijt

Hi,
I have a large spreadsheet that sums a column if there is an "x" in another column:

=SUMIF($J102:$J302,"x",$AL102:$AL302)

I need to change some of the "x"'s to numbers but I would stil like to sum in the same manner instead of doing something like this:

=SUMIF($J102:$J302,"x",$AL102:$AL302)+SUMIF($J102:$J302,">0",$AL102:$AL302)

As I have many cells that sum other columns based on the "x" criteria, I was hoping to use some sort of wildcard that recognizes numbers and text??

thanks.
 
Recognizing text and numbers is not good enough, as your criteria are either an "x" or a number greater than zero. Try the form:



=SUM(SUMIF(B1:B4,{"x",">0"},A1:A4))



If you truly want to count if there is any text or any number in cell, that can be done also.
Click to expand...

i can't seem to get this to work. is this an array formula?
 
The formula is entered normally. Note the criteria are entered as an array constant.



Copy/Paste the contents of the formula bar containing the formula here.
Click to expand...

works great, thanks for your help
 
Back
Top