sumproduct!!!

  • Thread starter Thread starter nowfal
  • Start date Start date
N

nowfal

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 i
this matter much obliged.
the formula i tried i
=IF(Sheet1!A1:A14="P",IF(Sheet1!B1:B14="USD",SUM(Sheet1!C1:C14,)))
I want the result is 300. I know instead of the SUM something els
should do.
thanks in advance
nowfa
 
If I add an additional colomn first as A colomn for date, how will b
the formula, Further i need to be selected as a function TODAY(), i
it possible to add in the above formula
something like this-
=SUMPRODUCT(Sheet1!A:A=TODAY())*(Sheet1!B1:B14="P")*(Sheet1!C1:C14="USD")*(Sheet1!D1:D14))
or otherwise any vba code is possible. All the helps will be highl
appreciated.
by
nowfa
 
Your formula is OK, *except* for your column A range references, and a
missing Parenthesis.
Just change them to match the other columns.

=SUMPRODUCT((Sheet1!A1:A14=TODAY())*(Sheet1!B1:B14="P")*(Sheet1!C1:C14="USD"
)*(Sheet1!D1:D14))

Also, with this formula, you must have *true* XL dates in column A.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

If I add an additional colomn first as A colomn for date, how will be
the formula, Further i need to be selected as a function TODAY(), is
it possible to add in the above formula
something like this-
=SUMPRODUCT(Sheet1!A:A=TODAY())*(Sheet1!B1:B14="P")*(Sheet1!C1:C14="USD")*(S
heet1!D1:D14))
or otherwise any vba code is possible. All the helps will be highly
appreciated.
by
nowfal
 
Again with additional problem,
in the first part of the sumproduct is it possible to add another on
each colomn in the second,third and fourth line . ie

=SUMPRODUCT((Sheet1!A1:A14=TODAY())*(Sheet1!B1:B14,F1:F14
="P")*(Sheet1!C1:C14,G1:G14="USD"
)*(Sheet1!D1:D14,H1:H14))

I am getting some error, I need some help very badly.
thanks.
nowfal
 
I assume that you want *both* columns to match the criteria, NOT one *or*
the other, and simply sum D and H together:

=SUMPRODUCT((Sheet1!A1:A14=TODAY())*(Sheet1!B1:B14="P")*(Sheet1!F1:F14="P")*
(Sheet1!C1:C14="USD")*(Sheet1!G1:G14="USD"),Sheet1!D1:D14+Sheet1!H1:H14)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Again with additional problem,
in the first part of the sumproduct is it possible to add another one
each colomn in the second,third and fourth line . ie

=SUMPRODUCT((Sheet1!A1:A14=TODAY())*(Sheet1!B1:B14,F1:F14
="P")*(Sheet1!C1:C14,G1:G14="USD"
)*(Sheet1!D1:D14,H1:H14))

I am getting some error, I need some help very badly.
thanks.
nowfal.
 
Thanks Ragdyer, but the solution you gave is not working, because m
explanation is wrong. I need the crieteria which should check anothe
set of colomn, and it should not be in the same raw. What in you
latest solution is if the particular currency is in same row then i
works, but it would not be in same row. So, please use your knowledg
in this matter. The exact thing is i know very near, mine is th
limited knowledge.
thank you
nowfa
 
I'm sorry, but I really just don't understand what you need.

Would you care to try explaining again, with some examples of what you
have, and then, what you expect to happen (exact data to be returned).
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

Thanks Ragdyer, but the solution you gave is not working, because my
explanation is wrong. I need the crieteria which should check another
set of colomn, and it should not be in the same raw. What in your
latest solution is if the particular currency is in same row then it
works, but it would not be in same row. So, please use your knowledge
in this matter. The exact thing is i know very near, mine is the
limited knowledge.
thank you
nowfal
 
Hi
just as a guess :-)

=SUMPRODUCT((Sheet1!A1:A14=TODAY())*(Sheet1!B1:B14="P")*(Sheet1!F1:F14=
"P")*
((Sheet1!C1:C14="USD")+(Sheet1!G1:G14="USD")>0),Sheet1!D1:D14+Sheet1!H1
:H14)

If you want to sum column D+column H if either column C or column G
contains 'USD'

or another alternative:
=SUMPRODUCT((Sheet1!A1:A14=TODAY())*(Sheet1!B1:B14="P")*(Sheet1!F1:F14=
"P")*
(Sheet1!C1:C14="USD"),Sheet1!D1:D14)
+SUMPRODUCT((Sheet1!A1:A14=TODAY())*(Sheet1!B1:B14="P")*(Sheet1!F1:F14=
"P")*
(Sheet1!G1:G14="USD"),Sheet1!H1:H14)

Sums column D if column C contains 'USD' AND adds column H if column G
contains 'USD'. If neiher returns the correct result for you just
follow RagDyer's suggestion to post some example data + expected
results


--
Regards
Frank Kabel
Frankfurt, Germany

I'm sorry, but I really just don't understand what you need.

Would you care to try explaining again, with some examples of what you
have, and then, what you expect to happen (exact data to be
Click to expand...
returned).
 
Hi,
As ragDyer’s suggestion ,here is the explanation.
It is a workbook with 5 sheets. In which the Master sheet name
“RECORD” is the subjected sheet. I am using 23 coloumns, out of whic
12 colomns are using for to create a report which is very useful, whic
is done by the assistance of you great people. In the above said shee
I have a master row which act as a form when I am entering the dat
into it. And a macro will copy it to the next row as well as giv
printing command to another sheet for bill.. In the meantime the ne
sumproduct gives the position of the currency I have sold to the thir
sheet(purchase and sale in different sheets. This problem comes when
customer wanted two three item of currency at a time. I am using th
‘PQRSTUVWX’ colomns for the second and third currency. So, now I hav
a doubt arise does it effect the blank cell to the sumproduct formula
if so I have so many blank cells in the second and third currenc
colomn.
A I J K M
DATE P/S FCY QUANTITY LCY
26/08/ P USD 1000 384
26/08/ P GBP 100 68
26/08/ P AED 1000 104.5
26/08 P AED 500 52.5
26/08/ P USD 300 115.2
26/08/ P USD 100 38.4
----------------------------------------------------------------
O P Q S
P/S IInd FCY QUANTITY LCY
P GBP 100 68


P USD 500 192
P AED 1000 104.5

--------------------------------------------------------------------
T U V X
P/S IIIrd FCY QUNATITY LCY
P INR 2000 17



P INR 10000 83
------------------------------------------------------------

I have split the row into three

IJKM Colomns will have data always, but remaining colomn may not have
I think the empty cells may be creating the problem.

=SUMPRODUCT((RECORD!A3:A200=TODAY())*(RECORD!I3:I200="P")*(RECORD!J3:J200="USD")*(RECORD!K3:K200))

USD Should be equal to 1900.
I think you got it some idea about this. The last reply of Mr.Fran
also i have tried, but failed. so. please have a look on it and do th
possible solution.
thanks a lot
by
nowfa
 
Why don't you send me a copy of your "Record" worksheet.

In my address, just cut out "cutout".
 
Hi,
Big Heart Ragdyer, i really appreciate you, having a very friendly
attitude to help people like me. sorry to say it was my fault had not
been tried correctly and asking for help. Luckily i got the result it
is like this

=SUMPRODUCT((RECORD!A3:A200=TODAY())*(RECORD!I3:I200="P")*(RECORD!J3:J200="AED")*(RECORD!K3:K200))+SUMPRODUCT((RECORD!A3:A200=TODAY())*(RECORD!O3:O200="P")*(RECORD!P3:P200="AED")*(RECORD!Q3:Q200))+SUMPRODUCT((RECORD!A3:A200=TODAY())*(RECORD!T3:T200="P")*(RECORD!U3:U200="AED")*(RECORD!V3:V200))

But still i wanted your help i will defintely send a copy of sheet
soon. Any possibility to reduce the above big formula.
anyway thanks to you,frank,je,and ken.
by
nowfal.
 
Try this one:-

=SUMPRODUCT((RECORD!A3:A200=TODAY())*(((RECORD!I3:I200="P")*(RECORD!J3:J200="AED")*(RECORD!K3:K200))+((RECORD!O3:O200="P")*(RECORD!P3:P200="AED")*(RECORD!Q3:Q200))+((RECORD!T3:T200="P")*(RECORD!U3:U200="AED")*(RECORD!V3:V200))))
 
Please have a look on the two formulas, In the second formula i am
getting a value error. I have tried lot and my guess is concentrated on
the last array. is there anything related with 'X' in formula. I mean if
X comes any problem.
First I am getting correct result as it is below:
=SUMPRODUCT((RECORD!A3:A200=TODAY())*(RECORD!I3:I200="P")*(RECORD!J3:J200="AED")*(RECORD!K3:K200))+SUMPRODUCT((RECORD!A3:A200=TODAY())*(RECORD!O3:O200="P")*(RECORD!P3:P200="AED")*(RECORD!Q3:Q200))+SUMPRODUCT((RECORD!A3:A200=TODAY())*(RECORD!T3:T200="P")*(RECORD!U3:U200="AED")*(RECORD!V3:V200))

Second one iam getting value error like this:
=SUMPRODUCT((RECORD!A3:A200=TODAY())*(RECORD!I3:I200="P")*(RECORD!J3:J200="AED")*(RECORD!M3:M200))+SUMPRODUCT((RECORD!A3:A200=TODAY())*(RECORD!O3:O200="P")*(RECORD!P3:P200="AED")*(RECORD!S3:S200))+SUMPRODUCT((RECIR!A3:A200=TODAY())*(RECORD!T3:T200="P")*(RECORD!U3:U200="AED")*(RECORD!X3:X200))

If i am removing the last array the formula is accepting but the result
is not correct. I am expecting an expert vision on this.
thank you.
nowfal
 
Usual reason for a #VALUE error in a SUMPRODUCT formula is because you have
include a range of values that have a text field in them, eg a header. If that
is the case then adjust the ranges to exclude the header fields. If it is not a
header then check for any possible text values anywhere in that range. Assuming
Col X is alll numeric, In a blank column simply try =TEXT(X3) and copy down -
Any TRUEs will be a problem. A space in a cell that looks blank will also do
this.

lastly I noticed that the sheet reference in one of your ranges has changed - is
that correct?? It says RECIRC and not RECORD, and whilst the range is the right
size, if you have a RECIRC sheet but have text in that range then again that
could be the cause.
 
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

vlook/match/index 12
triple sorting and twice sum 1
logic doubts 3
Need urgent help with SUMPRODUCT() 8
Avoiding circular reference on formula 4
14 Day Average 4
Programmatically create totals and names?? 2
calculating SUM with two conditions 1

Back
Top