Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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" wrote in message ... 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 |
#3
|
|||
|
|||
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" wrote in message ... 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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
On Fri, 24 Jun 2005 09:35:01 -0700, "Steve o"
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 |
#7
|
|||
|
|||
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" 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 |
#8
|
|||
|
|||
On Fri, 24 Jun 2005 11:17:02 -0700, "Steve o"
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 |
#9
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How I set up as logical test if my range has to be >=2 but <5 | Excel Discussion (Misc queries) | |||
How do I use Roundup in a logical test in excel | Excel Worksheet Functions | |||
=IF logical test to search only part of a cell | Excel Worksheet Functions | |||
Want to change the color of a true/false logical statement with i. | Excel Worksheet Functions | |||
logical test - within a range | Excel Worksheet Functions |