ExcelBanter - View Single Post

Ron Rosenfeld[_2_]

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.