sumif wildcard for text and numbers
 

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.

sumif wildcard for text and numbers
 

On Fri, 24 Aug 2012 05:10:17 -0700 (PDT), wrote:

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.

sumif wildcard for text and numbers
 

On Friday, August 24, 2012 10:04:56 AM UTC-4, Ron Rosenfeld wrote:
On Fri, 24 Aug 2012 05:10:17 -0700 (PDT), wrote:



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.

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

sumif wildcard for text and numbers
 

On Fri, 24 Aug 2012 07:22:43 -0700 (PDT), wrote:

On Friday, August 24, 2012 10:04:56 AM UTC-4, Ron Rosenfeld wrote:
On Fri, 24 Aug 2012 05:10:17 -0700 (PDT), wrote:



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.

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.

sumif wildcard for text and numbers
 

On Friday, August 24, 2012 11:59:01 AM UTC-4, Ron Rosenfeld wrote:
On Fri, 24 Aug 2012 07:22:43 -0700 (PDT), wrote:



On Friday, August 24, 2012 10:04:56 AM UTC-4, Ron Rosenfeld wrote:

On Fri, 24 Aug 2012 05:10:17 -0700 (PDT), wrote:







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.



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.

works great, thanks for your help

sumif wildcard for text and numbers
 

On Sat, 25 Aug 2012 06:49:01 -0700 (PDT), wrote:

works great, thanks for your help

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