If statement where the logical test is a range that equals a word

I am trying to figure out how I can return all values in a column using a
term in quotations. Basically, the column is date. I want to return values
that fall within a certain month. I am typing in =if(c2:c6421="01/*",
sumif(......),0) to get the values that fall within january, it seems right
to me but is returning an error. Can you type in a range that is equal to a
term in quotations? I don't see why not, but I get an error everytime, even
when I run a more simple test. Any suggestions would be greatly appreciated.
Thanks!
--
Steve Root
MCI

Steve,

You can use something like

=SUM(IF(MONTH(A1:A20)=1, B1:B20))

which is an array formula so commit with Ctrl-Shft-Enter. Or

=SUMPRODUCT(--(MONTH(A1:A20)=1),B1:B20)

which is not

--
HTH

Bob Phillips

"Steve o" <[email protected]> wrote in message
news:[email protected]...
> I am trying to figure out how I can return all values in a column using a
> term in quotations. Basically, the column is date. I want to return
values
> that fall within a certain month. I am typing in =if(c2:c6421="01/*",
> sumif(......),0) to get the values that fall within january, it seems
right
> to me but is returning an error. Can you type in a range that is equal to
a
> term in quotations? I don't see why not, but I get an error everytime,
even
> when I run a more simple test. Any suggestions would be greatly
appreciated.
> Thanks!
> --
> Steve Root
> MCI

Thanks, I'll give that a shot!
--
Steve Root
MCI


"Bob Phillips" wrote:

> Steve,
>
> You can use something like
>
> =SUM(IF(MONTH(A1:A20)=1, B1:B20))
>
> which is an array formula so commit with Ctrl-Shft-Enter. Or
>
> =SUMPRODUCT(--(MONTH(A1:A20)=1),B1:B20)
>
> which is not
>
> --
> HTH
>
> Bob Phillips
>
> "Steve o" <[email protected]> wrote in message
> news:[email protected]...
> > I am trying to figure out how I can return all values in a column using a
> > term in quotations. Basically, the column is date. I want to return
> values
> > that fall within a certain month. I am typing in =if(c2:c6421="01/*",
> > sumif(......),0) to get the values that fall within january, it seems
> right
> > to me but is returning an error. Can you type in a range that is equal to
> a
> > term in quotations? I don't see why not, but I get an error everytime,
> even
> > when I run a more simple test. Any suggestions would be greatly
> appreciated.
> > Thanks!
> > --
> > Steve Root
> > MCI
>
>
>

Hi Steve,

My understanding is that if you enter values in quotes, it will search for
text only. Your better bet might be the MONTH function:

=IF(MONTH(C2)=1,<FORUMLUA IF TRUE>,<FORMULA IF FALSE>)

You'd need to create separate columns for each month using this technique.

I hope this helps -


"Steve o" wrote:

> I am trying to figure out how I can return all values in a column using a
> term in quotations. Basically, the column is date. I want to return values
> that fall within a certain month. I am typing in =if(c2:c6421="01/*",
> sumif(......),0) to get the values that fall within january, it seems right
> to me but is returning an error. Can you type in a range that is equal to a
> term in quotations? I don't see why not, but I get an error everytime, even
> when I run a more simple test. Any suggestions would be greatly appreciated.
> Thanks!
> --
> Steve Root
> MCI

My goal is to have a formula that will look up a date and multiply the
corresponding infomation. For example, look up any january in the date
column that is Product A and multiply that times the corresponding quantity
(3 different columns (date, product, quantity). I need this formula to be
able to handle the sheet even if data is changed, ie changing the number of
records in the worksheet. So if in the future the data for january grows,
the formula will still be able to locate any products in january that the
formula specifies and then multiply that times the corresponding quantity.
It's a tad bit complicated.
--
Steve Root
MCI


"Dr. Nonverbal" wrote:

> Hi Steve,
>
> My understanding is that if you enter values in quotes, it will search for
> text only. Your better bet might be the MONTH function:
>
> =IF(MONTH(C2)=1,<FORUMLUA IF TRUE>,<FORMULA IF FALSE>)
>
> You'd need to create separate columns for each month using this technique.
>
> I hope this helps -
>
>
> "Steve o" wrote:
>
> > I am trying to figure out how I can return all values in a column using a
> > term in quotations. Basically, the column is date. I want to return values
> > that fall within a certain month. I am typing in =if(c2:c6421="01/*",
> > sumif(......),0) to get the values that fall within january, it seems right
> > to me but is returning an error. Can you type in a range that is equal to a
> > term in quotations? I don't see why not, but I get an error everytime, even
> > when I run a more simple test. Any suggestions would be greatly appreciated.
> > Thanks!
> > --
> > Steve Root
> > MCI

On Fri, 24 Jun 2005 09:35:01 -0700, "Steve o"
<[email protected]> wrote:

>My goal is to have a formula that will look up a date and multiply the
>corresponding infomation. For example, look up any january in the date
>column that is Product A and multiply that times the corresponding quantity
>(3 different columns (date, product, quantity). I need this formula to be
>able to handle the sheet even if data is changed, ie changing the number of
>records in the worksheet. So if in the future the data for january grows,
>the formula will still be able to locate any products in january that the
>formula specifies and then multiply that times the corresponding quantity.
>It's a tad bit complicated.
>--

How about:

Assum your columns are named date, product and quantity, the product that you
are looking for is in cell A1, and the month you are looking for is in cell A2
represented by the date number (e.g. 2=February)

=SUMPRODUCT((A2=MONTH(Date))*(A1=Product)*Quantity)

Or you could use a pivot table.



--ron

I guess what my basic question is "Can you make a range (ie, b2:b6000) equal
text?" For example, if(b2:b6000="01/*","good","bad") , making it so that
when it looks through column b any date that pops up would be in january,
then showing the result 'good.' Does b2:b6000 need to be in( )? Whenever I
type in a formula like the one above it gives me an answer of 'bad', when I
know full well there are plenty of january's in the b column.
--
Steve Root
MCI


"Ron Rosenfeld" wrote:

> On Fri, 24 Jun 2005 09:35:01 -0700, "Steve o"
> <[email protected]> wrote:
>
> >My goal is to have a formula that will look up a date and multiply the
> >corresponding infomation. For example, look up any january in the date
> >column that is Product A and multiply that times the corresponding quantity
> >(3 different columns (date, product, quantity). I need this formula to be
> >able to handle the sheet even if data is changed, ie changing the number of
> >records in the worksheet. So if in the future the data for january grows,
> >the formula will still be able to locate any products in january that the
> >formula specifies and then multiply that times the corresponding quantity.
> >It's a tad bit complicated.
> >--
>
> How about:
>
> Assum your columns are named date, product and quantity, the product that you
> are looking for is in cell A1, and the month you are looking for is in cell A2
> represented by the date number (e.g. 2=February)
>
> =SUMPRODUCT((A2=MONTH(Date))*(A1=Product)*Quantity)
>
> Or you could use a pivot table.
>
>
>
> --ron
>

On Fri, 24 Jun 2005 11:17:02 -0700, "Steve o"
<[email protected]> wrote:

>I guess what my basic question is "Can you make a range (ie, b2:b6000) equal
>text?" For example, if(b2:b6000="01/*","good","bad") , making it so that
>when it looks through column b any date that pops up would be in january,
>then showing the result 'good.' Does b2:b6000 need to be in( )? Whenever I
>type in a formula like the one above it gives me an answer of 'bad', when I
>know full well there are plenty of january's in the b column.
>--

In general, you can only look for text if there is text stored in your cells.
In Excel, formatting only controls what you see displayed; it has no effect on
what is stored.

Also, I don't believe that wild cards are acceptable in a simple equality of
the type =B10="01/*"

To do what you want, and assuming that the data in column B is stored as Dates,
and not as text strings, would require a more complicated formula.

I believe the formula I posted will do what you requested in your 12:35PM
message. At least you haven't posted back any problems with the formula.

Why do you want to make it more complicated?

As an exercise, you could use an array formula to convert the contents of
b2:b6000 to text, and then look at the first two characters of that text string
in your IF statement.


--ron

Using the month function works great. However, I only get results when I
type in month=1 and all that does is total up all the values that fall under
my specified category, instead of totaling up January. If I type in month=2,
the result comes up as false, as opposed to feb values - which is what I
thought the 2 stood for. Any ideas of why this would be so? Thanks.
--
Steve Root
MCI


"Dr. Nonverbal" wrote:

> Hi Steve,
>
> My understanding is that if you enter values in quotes, it will search for
> text only. Your better bet might be the MONTH function:
>
> =IF(MONTH(C2)=1,<FORUMLUA IF TRUE>,<FORMULA IF FALSE>)
>
> You'd need to create separate columns for each month using this technique.
>
> I hope this helps -
>
>
> "Steve o" wrote:
>
> > I am trying to figure out how I can return all values in a column using a
> > term in quotations. Basically, the column is date. I want to return values
> > that fall within a certain month. I am typing in =if(c2:c6421="01/*",
> > sumif(......),0) to get the values that fall within january, it seems right
> > to me but is returning an error. Can you type in a range that is equal to a
> > term in quotations? I don't see why not, but I get an error everytime, even
> > when I run a more simple test. Any suggestions would be greatly appreciated.
> > Thanks!
> > --
> > Steve Root
> > MCI