sumif wildcard for text and numbers

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

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.
Click to expand...

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

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.
Click to expand...

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

Ron Rosenfeld

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

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

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.
Click to expand...

works great, thanks for your help
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Use TODAY() function as the criteria in a SUMIF formula 1
Template with fields and premade texts.. 0
SUMIF with criteria "<>" & "=" 4
Wildcards in SumIF formula 1
Sum data using sumif 3
SumIf Question 8
Six columns randon no.'s no dupes 6
Excel does not accept text format! 0

Top