Sumif on Formula instead of Value

What I'm looking for is a way to sum the cells that contain =Ceiling.

I've tried SUMIF(range,"*CEILING*",range) but the formula returns 0. I'm
guessing it has to do with the fact that it is looking at the resulting value
of the cell instead of the formula.

Does anybody know if there is a way to have it search the formula?

Thanks in advance.

If CEILING is a range name for a single value, try this:

=SUMIF(range1,CEILING,range2)
That will sum the range2 items where the range1 items equal the value of
CEILING.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"adodson" wrote:

What I'm looking for is a way to sum the cells that contain =Ceiling.

I've tried SUMIF(range,"*CEILING*",range) but the formula returns 0. I'm
guessing it has to do with the fact that it is looking at the resulting value
of the cell instead of the formula.

Does anybody know if there is a way to have it search the formula?

Thanks in advance.

Sorry, I wasn't clear -- CEILING refers to the function inside Excel to round
to a given number.

An example of a cell I would want it to sum is:
=CEILING(A24,5)

"Ron Coderre" wrote:

If CEILING is a range name for a single value, try this:

=SUMIF(range1,CEILING,range2)
That will sum the range2 items where the range1 items equal the value of
CEILING.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"adodson" wrote:

What I'm looking for is a way to sum the cells that contain =Ceiling.

I've tried SUMIF(range,"*CEILING*",range) but the formula returns 0. I'm
guessing it has to do with the fact that it is looking at the resulting value
of the cell instead of the formula.

Does anybody know if there is a way to have it search the formula?

Thanks in advance.

I don't believe Excel has any built-in functions that scan cell formula
structures.

For that functionality you'd likely need a VBA solution.
***********
Regards,
Ron

XL2002, WinXP


"adodson" wrote:

Sorry, I wasn't clear -- CEILING refers to the function inside Excel to round
to a given number.

An example of a cell I would want it to sum is:
=CEILING(A24,5)

"Ron Coderre" wrote:

If CEILING is a range name for a single value, try this:

=SUMIF(range1,CEILING,range2)
That will sum the range2 items where the range1 items equal the value of
CEILING.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"adodson" wrote:

What I'm looking for is a way to sum the cells that contain =Ceiling.

I've tried SUMIF(range,"*CEILING*",range) but the formula returns 0. I'm
guessing it has to do with the fact that it is looking at the resulting value
of the cell instead of the formula.

Does anybody know if there is a way to have it search the formula?

Thanks in advance.

You could use a helper column inserted to the right of your range and
the GET.CELL macro. First define a name "ShowFormula" using the
following reference.

=GET.CELL(6,INDIRECT("RC[-1]",FALSE))

In the first cell of the helper column type the formula =ShowFormula
and copy down your range. This will populate that with the formula of
the cell to the left. Then you can use the COUNTIF funtion. So say
your range to find the formula is A1:A10 and your helper column is
B1:B10 then:

=COUNTIF(B1:B10,"*CEILING*)


Does that help?

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=546764