Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Help for a formula using two conditions
Hi. Can anybody help me with a formula based on two conditions.
I have a spreadsheet with various columns (example shown below). Month Customer Contract Type Contract Value Yr Value Apr 04 xxx New £10,000 £2,000 Apr 04 xxx Upgrade £2,000 £500 May 04 xxx New £15,000 £5,000 I need to be able to total the 'Yr Value' for 'New' contracts in 'Apr 04'. In the example above, I expect to get £2,000 for Apr 04 and £5,000 for May 04. This could be more complicated as the spreadsheet I want to put the formula in is different from where the information is. I have tried various formula with no success. Below is an example of one that I have tried. =SUM(IF(('[New Revenue 04_05.xls]New Revenue'!$A$2:$A$300="Apr 04")*('[New Revenue 04_05.xls]New Revenue'!$D$2:$D$300="New"),'[New Revenue 04_05.xls]New Revenue'!$K$2:$K$300)) Thanks. |
#2
|
|||
|
|||
Hi
if the month value is not a date but a text value try: =SUMPRODUCT(--(A1:A100="Apr 04"),--(C1:C100="New"),E1:E100) Also see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Frank Kabel Frankfurt, Germany "harwookf" schrieb im Newsbeitrag ... Hi. Can anybody help me with a formula based on two conditions. I have a spreadsheet with various columns (example shown below). Month Customer Contract Type Contract Value Yr Value Apr 04 xxx New £10,000 £2,000 Apr 04 xxx Upgrade £2,000 £500 May 04 xxx New £15,000 £5,000 I need to be able to total the 'Yr Value' for 'New' contracts in 'Apr 04'. In the example above, I expect to get £2,000 for Apr 04 and £5,000 for May 04. This could be more complicated as the spreadsheet I want to put the formula in is different from where the information is. I have tried various formula with no success. Below is an example of one that I have tried. =SUM(IF(('[New Revenue 04_05.xls]New Revenue'!$A$2:$A$300="Apr 04")*('[New Revenue 04_05.xls]New Revenue'!$D$2:$D$300="New"),'[New Revenue 04_05.xls]New Revenue'!$K$2:$K$300)) Thanks. |
#3
|
|||
|
|||
Hi!
His formula should have worked if the date value was a=20 text string. He has it quoted in the formula. Try this: =3DSUMPRODUCT(--(TEXT(A1:A4,"mmm yy")=3D"apr 04"),-- (B1:B4=3D"new"),C1:C4) Biff -----Original Message----- Hi if the month value is not a date but a text value try: =3DSUMPRODUCT(--(A1:A100=3D"Apr 04"),-- (C1:C100=3D"New"),E1:E100) Also see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Frank Kabel Frankfurt, Germany "harwookf" schrieb=20 im Newsbeitrag ... Hi. Can anybody help me with a formula based on two=20 conditions. I have a spreadsheet with various columns (example=20 shown below). Month Customer Contract Type Contract Value Yr=20 Value Apr 04 xxx New =C2=A310,000 =20 =C2=A32,000 Apr 04 xxx Upgrade =C2=A32,000 =20 =C2=A3500 May 04 xxx New =C2=A315,000 =20 =C2=A35,000 I need to be able to total the 'Yr Value' for 'New'=20 contracts in 'Apr 04'. In the example above, I expect to get =C2=A32,000 for Apr=20 04 and =C2=A35,000 for May 04. This could be more complicated as the spreadsheet I=20 want to put the formula in is different from where the information is. I have tried various formula with no success. Below is=20 an example of one that I have tried. =3DSUM(IF(('[New Revenue 04_05.xls]New Revenue'! $A$2:$A$300=3D"Apr 04")*('[New Revenue 04_05.xls]New Revenue'!$D$2:$D$300=3D"New"),'[New=20 Revenue 04_05.xls]New Revenue'!$K$2:$K$300)) Thanks. . |
#4
|
|||
|
|||
How do I link it to the spreadsheet with the data. The one I am putting the
formula in is a 'Comparisons' as it pulls data from different sources, but the data is in 'New Revenue', etc Thanks. "Frank Kabel" wrote: Hi if the month value is not a date but a text value try: =SUMPRODUCT(--(A1:A100="Apr 04"),--(C1:C100="New"),E1:E100) Also see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Frank Kabel Frankfurt, Germany "harwookf" schrieb im Newsbeitrag ... Hi. Can anybody help me with a formula based on two conditions. I have a spreadsheet with various columns (example shown below). Month Customer Contract Type Contract Value Yr Value Apr 04 xxx New £10,000 £2,000 Apr 04 xxx Upgrade £2,000 £500 May 04 xxx New £15,000 £5,000 I need to be able to total the 'Yr Value' for 'New' contracts in 'Apr 04'. In the example above, I expect to get £2,000 for Apr 04 and £5,000 for May 04. This could be more complicated as the spreadsheet I want to put the formula in is different from where the information is. I have tried various formula with no success. Below is an example of one that I have tried. =SUM(IF(('[New Revenue 04_05.xls]New Revenue'!$A$2:$A$300="Apr 04")*('[New Revenue 04_05.xls]New Revenue'!$D$2:$D$300="New"),'[New Revenue 04_05.xls]New Revenue'!$K$2:$K$300)) Thanks. |
#5
|
|||
|
|||
What if the date value is a date but shown as mmm yy? I still don't
understand how this formula knows which spreadsheet to find the data from. "Biff" wrote: Hi! His formula should have worked if the date value was a text string. He has it quoted in the formula. Try this: =SUMPRODUCT(--(TEXT(A1:A4,"mmm yy")="apr 04"),-- (B1:B4="new"),C1:C4) Biff -----Original Message----- Hi if the month value is not a date but a text value try: =SUMPRODUCT(--(A1:A100="Apr 04"),-- (C1:C100="New"),E1:E100) Also see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Frank Kabel Frankfurt, Germany "harwookf" schrieb im Newsbeitrag ... Hi. Can anybody help me with a formula based on two conditions. I have a spreadsheet with various columns (example shown below). Month Customer Contract Type Contract Value Yr Value Apr 04 xxx New £10,000 £2,000 Apr 04 xxx Upgrade £2,000 £500 May 04 xxx New £15,000 £5,000 I need to be able to total the 'Yr Value' for 'New' contracts in 'Apr 04'. In the example above, I expect to get £2,000 for Apr 04 and £5,000 for May 04. This could be more complicated as the spreadsheet I want to put the formula in is different from where the information is. I have tried various formula with no success. Below is an example of one that I have tried. =SUM(IF(('[New Revenue 04_05.xls]New Revenue'! $A$2:$A$300="Apr 04")*('[New Revenue 04_05.xls]New Revenue'!$D$2:$D$300="New"),'[New Revenue 04_05.xls]New Revenue'!$K$2:$K$300)) Thanks. . |
#6
|
|||
|
|||
Hi
first use: =SUMPRODUCT(--(MONTH(A1:A100)=4),--(YEAR(A1:A100)=2004),--(C1:C100="New "),E1:E100) If you need to do this on a different worksheet you have to change the range references accordingly. e.g. =SUMPRODUCT(--(MONTH('other_sheet'!A1:A100)=4),--(YEAR('other_sheet'!A1 :A100)=2004),--('other_sheet'!C1:C100="New"),'other_sheet'!E1:E10 0) -- Regards Frank Kabel Frankfurt, Germany "harwookf" schrieb im Newsbeitrag ... What if the date value is a date but shown as mmm yy? I still don't understand how this formula knows which spreadsheet to find the data from. "Biff" wrote: Hi! His formula should have worked if the date value was a text string. He has it quoted in the formula. Try this: =SUMPRODUCT(--(TEXT(A1:A4,"mmm yy")="apr 04"),-- (B1:B4="new"),C1:C4) Biff -----Original Message----- Hi if the month value is not a date but a text value try: =SUMPRODUCT(--(A1:A100="Apr 04"),-- (C1:C100="New"),E1:E100) Also see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Frank Kabel Frankfurt, Germany "harwookf" schrieb im Newsbeitrag ... Hi. Can anybody help me with a formula based on two conditions. I have a spreadsheet with various columns (example shown below). Month Customer Contract Type Contract Value Yr Value Apr 04 xxx New £10,000 £2,000 Apr 04 xxx Upgrade £2,000 £500 May 04 xxx New £15,000 £5,000 I need to be able to total the 'Yr Value' for 'New' contracts in 'Apr 04'. In the example above, I expect to get £2,000 for Apr 04 and £5,000 for May 04. This could be more complicated as the spreadsheet I want to put the formula in is different from where the information is. I have tried various formula with no success. Below is an example of one that I have tried. =SUM(IF(('[New Revenue 04_05.xls]New Revenue'! $A$2:$A$300="Apr 04")*('[New Revenue 04_05.xls]New Revenue'!$D$2:$D$300="New"),'[New Revenue 04_05.xls]New Revenue'!$K$2:$K$300)) Thanks. . |
#7
|
|||
|
|||
Hi!
Use Frank's second formula if the dates are true dates. My formula just converts true dates into text strings. It=20 will work but Franks is better. His are always better <g The best way to get your ranges is to have the other=20 workbook open. As you enter the formula use the mouse to=20 select the ranges from the other workbook. That way, Excel=20 will automatically enter the full path saving you the=20 trouble of typing it in. Some peoples paths can be=20 ridiculosly long! Biff -----Original Message----- What if the date value is a date but shown as mmm yy? I=20 still don't=20 understand how this formula knows which spreadsheet to=20 find the data from. "Biff" wrote: Hi! =20 His formula should have worked if the date value was a=20 text string. He has it quoted in the formula. =20 Try this: =20 =3DSUMPRODUCT(--(TEXT(A1:A4,"mmm yy")=3D"apr 04"),-- (B1:B4=3D"new"),C1:C4) =20 Biff =20 -----Original Message----- Hi if the month value is not a date but a text value try: =3DSUMPRODUCT(--(A1:A100=3D"Apr 04"),-- (C1:C100=3D"New"),E1:E100) Also see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Frank Kabel Frankfurt, Germany "harwookf" =20 schrieb=20 im Newsbeitrag news:3A40AC9B-77D4-4DC8-90E4- ... Hi. Can anybody help me with a formula based on two=20 conditions. I have a spreadsheet with various columns (example=20 shown below). Month Customer Contract Type Contract Value =20 Yr=20 Value Apr 04 xxx New =20 =C3,=C2=A310,000 =20 =C3,=C2=A32,000 Apr 04 xxx Upgrade =20 =C3,=C2=A32,000 =20 =C3,=C2=A3500 May 04 xxx New =20 =C3,=C2=A315,000 =20 =C3,=C2=A35,000 I need to be able to total the 'Yr Value' for 'New'=20 contracts in 'Apr 04'. In the example above, I expect to get =C3,=C2=A32,000 for=20 Apr=20 04 and =C3,=C2=A35,000 for May 04. This could be more complicated as the spreadsheet I=20 want to put the formula in is different from where the information is. I have tried various formula with no success. Below=20 is=20 an example of one that I have tried. =3DSUM(IF(('[New Revenue 04_05.xls]New Revenue'! $A$2:$A$300=3D"Apr 04")*('[New Revenue 04_05.xls]New Revenue'! $D$2:$D$300=3D"New"),'[New=20 Revenue 04_05.xls]New Revenue'!$K$2:$K$300)) Thanks. . =20 . |
#8
|
|||
|
|||
Thanks very much to you both. I have now got the spreadsheet working and
formulating correctly. Had to change my date to text, but it works and I'm very happy. Just wished I posted the problem earlier as it has been puzzling me for days!! Thanks again. "Frank Kabel" wrote: Hi first use: =SUMPRODUCT(--(MONTH(A1:A100)=4),--(YEAR(A1:A100)=2004),--(C1:C100="New "),E1:E100) If you need to do this on a different worksheet you have to change the range references accordingly. e.g. =SUMPRODUCT(--(MONTH('other_sheet'!A1:A100)=4),--(YEAR('other_sheet'!A1 :A100)=2004),--('other_sheet'!C1:C100="New"),'other_sheet'!E1:E10 0) -- Regards Frank Kabel Frankfurt, Germany "harwookf" schrieb im Newsbeitrag ... What if the date value is a date but shown as mmm yy? I still don't understand how this formula knows which spreadsheet to find the data from. "Biff" wrote: Hi! His formula should have worked if the date value was a text string. He has it quoted in the formula. Try this: =SUMPRODUCT(--(TEXT(A1:A4,"mmm yy")="apr 04"),-- (B1:B4="new"),C1:C4) Biff -----Original Message----- Hi if the month value is not a date but a text value try: =SUMPRODUCT(--(A1:A100="Apr 04"),-- (C1:C100="New"),E1:E100) Also see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Frank Kabel Frankfurt, Germany "harwookf" schrieb im Newsbeitrag ... Hi. Can anybody help me with a formula based on two conditions. I have a spreadsheet with various columns (example shown below). Month Customer Contract Type Contract Value Yr Value Apr 04 xxx New £10,000 £2,000 Apr 04 xxx Upgrade £2,000 £500 May 04 xxx New £15,000 £5,000 I need to be able to total the 'Yr Value' for 'New' contracts in 'Apr 04'. In the example above, I expect to get £2,000 for Apr 04 and £5,000 for May 04. This could be more complicated as the spreadsheet I want to put the formula in is different from where the information is. I have tried various formula with no success. Below is an example of one that I have tried. =SUM(IF(('[New Revenue 04_05.xls]New Revenue'! $A$2:$A$300="Apr 04")*('[New Revenue 04_05.xls]New Revenue'!$D$2:$D$300="New"),'[New Revenue 04_05.xls]New Revenue'!$K$2:$K$300)) Thanks. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
revert formula insertion to old method | Setting up and Configuration of Excel | |||
Simple formula doesn't quite add up | Excel Discussion (Misc queries) | |||
Concatenating two conditions in a "IF" Formula. | New Users to Excel | |||
Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions | |||
Excel 2003 - Formula result shows as 0:00 | Excel Worksheet Functions |