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.

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.

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?

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.

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

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.