| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formulas
I am trying to write something like this:
If the date in column a = jan and the 1st letter of column h6 is a, add h6&h7 together, if not 0 -- DMM |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formulas
I assume that "jan" in your example if in row 6 of column a (otherwise you'll
need to make the necessary adjustment). =if(and(month(a6)=1,left(h6,1)="a"),h6+h7,0) Note that the date in a6 needs to be a date/date serial #, not text. Will "soconfused" wrote: I am trying to write something like this: If the date in column a = jan and the 1st letter of column h6 is a, add h6&h7 together, if not 0 -- DMM |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formulas
That's not working and I don't know why? Any other ideas?
-- DMM "roadkill" wrote: I assume that "jan" in your example if in row 6 of column a (otherwise you'll need to make the necessary adjustment). =if(and(month(a6)=1,left(h6,1)="a"),h6+h7,0) Note that the date in a6 needs to be a date/date serial #, not text. Will "soconfused" wrote: I am trying to write something like this: If the date in column a = jan and the 1st letter of column h6 is a, add h6&h7 together, if not 0 -- DMM |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formulas
If h6 begins with the letter a then it must be text, so you will have
to change the + sign to & (in which case you probably also want "0" instead of just 0), like so: =if(and(month(a6)=1,left(h6,1)="a"),h6&h7,"0") Hope this helps. Pete On Aug 28, 9:12 pm, soconfused wrote: That's not working and I don't know why? Any other ideas? -- DMM "roadkill" wrote: I assume that "jan" in your example if in row 6 of column a (otherwise you'll need to make the necessary adjustment). =if(and(month(a6)=1,left(h6,1)="a"),h6+h7,0) Note that the date in a6 needs to be a date/date serial #, not text. Will "soconfused" wrote: I am trying to write something like this: If the date in column a = jan and the 1st letter of column h6 is a, add h6&h7 together, if not 0 -- DMM- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formulas
Hi Pete,
One more problem, is there any way to make the h6 a range, such as H6:H25, I tried that, but it didn't work. In other words, If Column A is Feb and column H6 through H25 contains an "A", sum AX6 through AX25? I can't seem to get it to work. Any help at all would be greatly appreciated. Thanks, D -- DMM "Pete_UK" wrote: If h6 begins with the letter a then it must be text, so you will have to change the + sign to & (in which case you probably also want "0" instead of just 0), like so: Hope this helps. Pete On Aug 28, 9:12 pm, soconfused wrote: That's not working and I don't know why? Any other ideas? -- DMM "roadkill" wrote: I assume that "jan" in your example if in row 6 of column a (otherwise you'll need to make the necessary adjustment). =if(and(month(a6)=1,left(h6,1)="a"),h6+h7,0) Note that the date in a6 needs to be a date/date serial #, not text. Will "soconfused" wrote: I am trying to write something like this: If the date in column a = jan and the 1st letter of column h6 is a, add h6&h7 together, if not 0 -- DMM- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formulas
Try this:
=SUMPRODUCT((MONTH(A6:A25)=2)*(LEFT(H6:H25,1)="A") *(AX6:AX25)) Assumes proper dates in column A and column H begins with "A". Hope this helps. Pete On Aug 29, 12:56 pm, soconfused wrote: Hi Pete, One more problem, is there any way to make the h6 a range, such as H6:H25, I tried that, but it didn't work. In other words, If Column A is Feb and column H6 through H25 contains an "A", sum AX6 through AX25? I can't seem to get it to work. Any help at all would be greatly appreciated. Thanks, D -- DMM "Pete_UK" wrote: If h6 begins with the letter a then it must be text, so you will have to change the + sign to & (in which case you probably also want "0" instead of just 0), like so: Hope this helps. Pete On Aug 28, 9:12 pm, soconfused wrote: That's not working and I don't know why? Any other ideas? -- DMM "roadkill" wrote: I assume that "jan" in your example if in row 6 of column a (otherwise you'll need to make the necessary adjustment). =if(and(month(a6)=1,left(h6,1)="a"),h6+h7,0) Note that the date in a6 needs to be a date/date serial #, not text. Will "soconfused" wrote: I am trying to write something like this: If the date in column a = jan and the 1st letter of column h6 is a, add h6&h7 together, if not 0 -- DMM- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formulas
Thank you, Thank you, Thank you, you have just saved me so many hours of
calculations and errors. -- DMM "Pete_UK" wrote: Try this: =SUMPRODUCT((MONTH(A6:A25)=2)*(LEFT(H6:H25,1)="A") *(AX6:AX25)) Assumes proper dates in column A and column H begins with "A". Hope this helps. Pete On Aug 29, 12:56 pm, soconfused wrote: Hi Pete, One more problem, is there any way to make the h6 a range, such as H6:H25, I tried that, but it didn't work. In other words, If Column A is Feb and column H6 through H25 contains an "A", sum AX6 through AX25? I can't seem to get it to work. Any help at all would be greatly appreciated. Thanks, D -- DMM "Pete_UK" wrote: If h6 begins with the letter a then it must be text, so you will have to change the + sign to & (in which case you probably also want "0" instead of just 0), like so: Hope this helps. Pete On Aug 28, 9:12 pm, soconfused wrote: That's not working and I don't know why? Any other ideas? -- DMM "roadkill" wrote: I assume that "jan" in your example if in row 6 of column a (otherwise you'll need to make the necessary adjustment). =if(and(month(a6)=1,left(h6,1)="a"),h6+h7,0) Note that the date in a6 needs to be a date/date serial #, not text. Will "soconfused" wrote: I am trying to write something like this: If the date in column a = jan and the 1st letter of column h6 is a, add h6&h7 together, if not 0 -- DMM- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formulas
Thank you, thank you, thank you, you have just saved me so many hours of
calculation work and errors. -- DMM "Pete_UK" wrote: Try this: =SUMPRODUCT((MONTH(A6:A25)=2)*(LEFT(H6:H25,1)="A") *(AX6:AX25)) Assumes proper dates in column A and column H begins with "A". Hope this helps. Pete On Aug 29, 12:56 pm, soconfused wrote: Hi Pete, One more problem, is there any way to make the h6 a range, such as H6:H25, I tried that, but it didn't work. In other words, If Column A is Feb and column H6 through H25 contains an "A", sum AX6 through AX25? I can't seem to get it to work. Any help at all would be greatly appreciated. Thanks, D -- DMM "Pete_UK" wrote: If h6 begins with the letter a then it must be text, so you will have to change the + sign to & (in which case you probably also want "0" instead of just 0), like so: Hope this helps. Pete On Aug 28, 9:12 pm, soconfused wrote: That's not working and I don't know why? Any other ideas? -- DMM "roadkill" wrote: I assume that "jan" in your example if in row 6 of column a (otherwise you'll need to make the necessary adjustment). =if(and(month(a6)=1,left(h6,1)="a"),h6+h7,0) Note that the date in a6 needs to be a date/date serial #, not text. Will "soconfused" wrote: I am trying to write something like this: If the date in column a = jan and the 1st letter of column h6 is a, add h6&h7 together, if not 0 -- DMM- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formulas
So good you had to say it twice !! <bg Thanks for feeding back.
Pete On Aug 29, 1:32 pm, soconfused wrote: Thank you, thank you, thank you, you have just saved me so many hours of calculation work and errors. -- DMM "Pete_UK" wrote: Try this: =SUMPRODUCT((MONTH(A6:A25)=2)*(LEFT(H6:H25,1)="A") *(AX6:AX25)) Assumes proper dates in column A and column H begins with "A". Hope this helps. Pete On Aug 29, 12:56 pm, soconfused wrote: Hi Pete, One more problem, is there any way to make the h6 a range, such as H6:H25, I tried that, but it didn't work. In other words, If Column A is Feb and column H6 through H25 contains an "A", sum AX6 through AX25? I can't seem to get it to work. Any help at all would be greatly appreciated. Thanks, D -- DMM "Pete_UK" wrote: If h6 begins with the letter a then it must be text, so you will have to change the + sign to & (in which case you probably also want "0" instead of just 0), like so: Hope this helps. Pete On Aug 28, 9:12 pm, soconfused wrote: That's not working and I don't know why? Any other ideas? -- DMM "roadkill" wrote: I assume that "jan" in your example if in row 6 of column a (otherwise you'll need to make the necessary adjustment). =if(and(month(a6)=1,left(h6,1)="a"),h6+h7,0) Note that the date in a6 needs to be a date/date serial #, not text. Will "soconfused" wrote: I am trying to write something like this: If the date in column a = jan and the 1st letter of column h6 is a, add h6&h7 together, if not 0 -- DMM- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formulas
One more question? Is there a way to make this equation look at ranges??
-- DMM "Pete_UK" wrote: So good you had to say it twice !! <bg Thanks for feeding back. Pete On Aug 29, 1:32 pm, soconfused wrote: Thank you, thank you, thank you, you have just saved me so many hours of calculation work and errors. -- DMM "Pete_UK" wrote: Try this: =SUMPRODUCT((MONTH(A6:A25)=2)*(LEFT(H6:H25,1)="A") *(AX6:AX25)) Assumes proper dates in column A and column H begins with "A". Hope this helps. Pete On Aug 29, 12:56 pm, soconfused wrote: Hi Pete, One more problem, is there any way to make the h6 a range, such as H6:H25, I tried that, but it didn't work. In other words, If Column A is Feb and column H6 through H25 contains an "A", sum AX6 through AX25? I can't seem to get it to work. Any help at all would be greatly appreciated. Thanks, D -- DMM "Pete_UK" wrote: If h6 begins with the letter a then it must be text, so you will have to change the + sign to & (in which case you probably also want "0" instead of just 0), like so: Hope this helps. Pete On Aug 28, 9:12 pm, soconfused wrote: That's not working and I don't know why? Any other ideas? -- DMM "roadkill" wrote: I assume that "jan" in your example if in row 6 of column a (otherwise you'll need to make the necessary adjustment). =if(and(month(a6)=1,left(h6,1)="a"),h6+h7,0) Note that the date in a6 needs to be a date/date serial #, not text. Will "soconfused" wrote: I am trying to write something like this: If the date in column a = jan and the 1st letter of column h6 is a, add h6&h7 together, if not 0 -- DMM- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| CELLS NOT CALC FORMULAS - VALUES STAY SME FORMULAS CORRECT?? HELP | Excel Worksheet Functions | |||
| lookup formulas dependent upon lookup formulas | Excel Worksheet Functions | |||
| automatically copy formulas down columns or copy formulas all the | Excel Worksheet Functions | |||
| Formulas not evaluated, Formulas treated as strings | Excel Discussion (Misc queries) | |||
| formulas for changing formulas? | Excel Discussion (Misc queries) | |||
Linear Mode
