sumif wildcard for text and numbers

[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.

 

[Ron Rosenfeld]

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.

 

[kijijijt]

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.

i can't seem to get this to work. is this an array formula?

 

[Ron Rosenfeld]

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.

 

[kijijijt]

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.

works great, thanks for your help

 

[Ron Rosenfeld]

works great, thanks for your help

Glad you've got it working. Thanks for the feedback.