sumproduct!!!

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/

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/

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