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

[Guest]

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!

 

[Bob Phillips]

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

 

[Guest]

Thanks, I'll give that a shot!
--
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

 

[Guest]

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 -

 

[Guest]

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.

 

[Ron Rosenfeld]

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

 

[Guest]

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.

 

[Ron Rosenfeld]

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

 

[Guest]

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.