Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumproduct 2 columns based on criteria in 3rd column
Can someone help me to with this formula?
I have 3 columns. Two of them have dollar amounts and the third one a date. How to I add the 2 columns based on the date in the third column without counting blank fields. A 4,228.50 B 295.00 C January 12, 2005 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumproduct 2 columns based on criteria in 3rd column
Do you want to SUM the A and B values for all non-blank C's ?
Gives us a little more info. -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "excel guru i''m not" wrote in message ... Can someone help me to with this formula? I have 3 columns. Two of them have dollar amounts and the third one a date. How to I add the 2 columns based on the date in the third column without counting blank fields. A 4,228.50 B 295.00 C January 12, 2005 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumproduct 2 columns based on criteria in 3rd column
For the 3 columns I am trying to add by month the amount of money paid. So I
would be paid $4,288.50 in column A plus $295 in column B and I need it to add those two together for the month, each month. However the rest of rows may be blank until dollars are paid and entered into those rows. So I don't want it to count the blank rows as January. Does that make better sense? "Bernard Liengme" wrote: Do you want to SUM the A and B values for all non-blank C's ? Gives us a little more info. -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "excel guru i''m not" wrote in message ... Can someone help me to with this formula? I have 3 columns. Two of them have dollar amounts and the third one a date. How to I add the 2 columns based on the date in the third column without counting blank fields. A 4,228.50 B 295.00 C January 12, 2005 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumproduct 2 columns based on criteria in 3rd column
Right now this is my formula and it is giving me a dollar amount over $1
million when the answer should be $4228.50 plus $295. =SUMPRODUCT($I$4:$I$97,--(MONTH($L$4:$L$97)=1),--($L$4:$L$97<"")+SUMPRODUCT(N4:N97,--(MONTH(L4:L97)=1),--(L4:L97<""))) "Bernard Liengme" wrote: Do you want to SUM the A and B values for all non-blank C's ? Gives us a little more info. -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "excel guru i''m not" wrote in message ... Can someone help me to with this formula? I have 3 columns. Two of them have dollar amounts and the third one a date. How to I add the 2 columns based on the date in the third column without counting blank fields. A 4,228.50 B 295.00 C January 12, 2005 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumproduct 2 columns based on criteria in 3rd column
Your formula would have worked fine if you had got the brackets in the right
place =SUMPRODUCT($I$4:$I$97,--(MONTH($L$4:$L$97)=1),--($L$4:$L$97<""))+SUMPRODUC T(N4:N97,--(MONTH(L4:L97)=1),--(L4:L97<"")) But it can be done better with =SUMPRODUCT(--(MONTH($L$4:$L$97)=1),--($L$4:$L$97<""),($I$4:$I$97+$N$4:$N$9 7)) -- HTH RP (remove nothere from the email address if mailing direct) "excel guru i''m not" wrote in message ... Right now this is my formula and it is giving me a dollar amount over $1 million when the answer should be $4228.50 plus $295. =SUMPRODUCT($I$4:$I$97,--(MONTH($L$4:$L$97)=1),--($L$4:$L$97<"")+SUMPRODUCT (N4:N97,--(MONTH(L4:L97)=1),--(L4:L97<""))) "Bernard Liengme" wrote: Do you want to SUM the A and B values for all non-blank C's ? Gives us a little more info. -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "excel guru i''m not" wrote in message ... Can someone help me to with this formula? I have 3 columns. Two of them have dollar amounts and the third one a date. How to I add the 2 columns based on the date in the third column without counting blank fields. A 4,228.50 B 295.00 C January 12, 2005 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumproduct 2 columns based on criteria in 3rd column
Like magic! I don't know how you all learned this stuff, but I sure am glad
you are there to help me! "Bob Phillips" wrote: Your formula would have worked fine if you had got the brackets in the right place =SUMPRODUCT($I$4:$I$97,--(MONTH($L$4:$L$97)=1),--($L$4:$L$97<""))+SUMPRODUC T(N4:N97,--(MONTH(L4:L97)=1),--(L4:L97<"")) But it can be done better with =SUMPRODUCT(--(MONTH($L$4:$L$97)=1),--($L$4:$L$97<""),($I$4:$I$97+$N$4:$N$9 7)) -- HTH RP (remove nothere from the email address if mailing direct) "excel guru i''m not" wrote in message ... Right now this is my formula and it is giving me a dollar amount over $1 million when the answer should be $4228.50 plus $295. =SUMPRODUCT($I$4:$I$97,--(MONTH($L$4:$L$97)=1),--($L$4:$L$97<"")+SUMPRODUCT (N4:N97,--(MONTH(L4:L97)=1),--(L4:L97<""))) "Bernard Liengme" wrote: Do you want to SUM the A and B values for all non-blank C's ? Gives us a little more info. -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "excel guru i''m not" wrote in message ... Can someone help me to with this formula? I have 3 columns. Two of them have dollar amounts and the third one a date. How to I add the 2 columns based on the date in the third column without counting blank fields. A 4,228.50 B 295.00 C January 12, 2005 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help w/ counting multiple columns based on IF criteria | Excel Worksheet Functions | |||
creating a bar graph | Excel Discussion (Misc queries) | |||
how to combine several columns into a single column | Excel Discussion (Misc queries) | |||
Arithmetical Mode of Criteria in Multiple Non-Adjacent columns | Excel Worksheet Functions | |||
Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions |