If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
Hi
what is the exact formula you have used? -- Regards Frank Kabel Frankfurt, Germany "Fad" schrieb im Newsbeitrag ... Hello, I have approximately the same example as Nowfal below but my data are on different excel files. So when I tried the function you gave below I always get #N/A although the file and sheet names are correct. Any idea why? Thanking you in advance. "JE McGimpsey" wrote: One way: =SUMPRODUCT(--(Sheet1!A1:A14="P"),--(Sheet1!B1:B14="USD"), Sheet1!C1:C14) In article , nowfal wrote: Hi, In the first sheet, i have the following data S INR 2000 17.6 P SAR 300 30.6 P USD 100 38.4 P USD 200 76.8 S GBP 100 71.5 S GBP 200 143 S EUR 100 47 P AED 200 20.9 In the second sheet it is to be sorted by formula, not by pivot table, i made a formula but it is giving wrong result, if somebody helps in this matter much obliged. the formula i tried is =IF(Sheet1!A1:A14="P",IF(Sheet1!B1:B14="USD",SUM(S heet1!C1:C14,))) I want the result is 300. I know instead of the SUM something else should do. thanks in advance nowfal --- Message posted from http://www.ExcelForum.com/ |
#22
|
|||
|
|||
Hello,
This is the formula that I used =SUMPRODUCT(--('P:\Gv\Data\xls\[040923-2HR-ProfileAnalysis.xls]02-Profile'!$D$100:$D$212="Ba"),--('P:\Gv\Data\xls\[040923-2HR-ProfileAnalysis.xls]02-Profile'!$J$100:$J$212="S"),'P:\Gv\Data\xls\[040923-2HR-ProfileAnalysis.xls]02-Profile'!$B$100:$B$212) Thank you for your assistance. "Frank Kabel" wrote: Hi what is the exact formula you have used? -- Regards Frank Kabel Frankfurt, Germany "Fad" schrieb im Newsbeitrag ... Hello, I have approximately the same example as Nowfal below but my data are on different excel files. So when I tried the function you gave below I always get #N/A although the file and sheet names are correct. Any idea why? Thanking you in advance. "JE McGimpsey" wrote: One way: =SUMPRODUCT(--(Sheet1!A1:A14="P"),--(Sheet1!B1:B14="USD"), Sheet1!C1:C14) In article , nowfal wrote: Hi, In the first sheet, i have the following data S INR 2000 17.6 P SAR 300 30.6 P USD 100 38.4 P USD 200 76.8 S GBP 100 71.5 S GBP 200 143 S EUR 100 47 P AED 200 20.9 In the second sheet it is to be sorted by formula, not by pivot table, i made a formula but it is giving wrong result, if somebody helps in this matter much obliged. the formula i tried is =IF(Sheet1!A1:A14="P",IF(Sheet1!B1:B14="USD",SUM(S heet1!C1:C14,))) I want the result is 300. I know instead of the SUM something else should do. thanks in advance nowfal --- Message posted from http://www.ExcelForum.com/ |
#23
|
|||
|
|||
Your formula worked ok for me.
I think you should look at your data and see if you have any errors (like #n/a) in: D100:d212, J100:J212 and B100:b212. Fad wrote: Hello, This is the formula that I used =SUMPRODUCT(--('P:\Gv\Data\xls\[040923-2HR-ProfileAnalysis.xls]02-Profile'!$D$100:$D$212="Ba"),--('P:\Gv\Data\xls\[040923-2HR-ProfileAnalysis.xls]02-Profile'!$J$100:$J$212="S"),'P:\Gv\Data\xls\[040923-2HR-ProfileAnalysis.xls]02-Profile'!$B$100:$B$212) Thank you for your assistance. "Frank Kabel" wrote: Hi what is the exact formula you have used? -- Regards Frank Kabel Frankfurt, Germany "Fad" schrieb im Newsbeitrag ... Hello, I have approximately the same example as Nowfal below but my data are on different excel files. So when I tried the function you gave below I always get #N/A although the file and sheet names are correct. Any idea why? Thanking you in advance. "JE McGimpsey" wrote: One way: =SUMPRODUCT(--(Sheet1!A1:A14="P"),--(Sheet1!B1:B14="USD"), Sheet1!C1:C14) In article , nowfal wrote: Hi, In the first sheet, i have the following data S INR 2000 17.6 P SAR 300 30.6 P USD 100 38.4 P USD 200 76.8 S GBP 100 71.5 S GBP 200 143 S EUR 100 47 P AED 200 20.9 In the second sheet it is to be sorted by formula, not by pivot table, i made a formula but it is giving wrong result, if somebody helps in this matter much obliged. the formula i tried is =IF(Sheet1!A1:A14="P",IF(Sheet1!B1:B14="USD",SUM(S heet1!C1:C14,))) I want the result is 300. I know instead of the SUM something else should do. thanks in advance nowfal --- Message posted from http://www.ExcelForum.com/ -- Dave Peterson |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Using a named range in a Sumproduct comparison | Hari | General Discussion | 7 | August 23rd, 2004 06:48 PM |
SUMIF vs SUM(IF(..)) vs SUMPRODUCT | Harlan Grove | Worksheet Functions | 7 | July 1st, 2004 10:22 PM |
SUMIF vs SUM(IF(..)) vs SUMPRODUCT | Harlan Grove | General Discussion | 7 | July 1st, 2004 10:22 PM |
Sumproduct and zero's..help please | www.ttdown.com | Worksheet Functions | 6 | March 21st, 2004 01:19 PM |
If & SumProduct | Otto Moehrbach | Worksheet Functions | 6 | November 14th, 2003 11:43 AM |