• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

sumproduct help needed

J

Jagadeeshbs

Member
Hello all


I have date like this

No Co Acct Description Typ Are Line Description Aug-11

399 51 121000 OTHER RECEIVABLES 0 ACCOUNTS RECEIVABLE OTHER 30,350

399 55 112000 EMPLOYEE RECEIVABLE 0 ACCOUNTS RECEIVABLE OTHER (3)

399 55 121000 OTHER RECEIVABLES 0 ACCOUNTS RECEIVABLE OTHER 509,319

And so onnn


And have reporting sheet like this

DESCRIPTION ACCOUNT COMPANY Aug 2011

EMPLOYEE RECEIVABLES 112000 37,54,55,56,61, 62, 66,75,79,90,91,2A,4A 317

EMPLOYEE RELOC LOAN 112001 61 -

INSURANCE RECEIVABLE - ENV 114000 62,69 -


How can I retrieve this form the main sheet using sumproduct…?


Please help me with formula
 
You don't show the relationship between the source and target data. SP is for summing, not listing.
 
I need to get the values in column J to the target data on the basis of given Company code and account number.

I was able to get the details where one co code is meniond but I don’t know how retrieve if there are more than one company code.


= (SUMPRODUCT((sheet1!E:E=report!B17)*(sheet1!B:B=report!C17)*(sheet1!J:J))
 
does this do it

=SUMPRODUCT((sheet1!E:E=report!B17)*(sheet1!B:B=report!C17),(sheet1!J:J)+ SUMPRODUCT((sheet1!E:E=report!B17)*(sheet1!B:B=report!C18),(sheet1!J:J)


assuming company 2 is in Report!C18

etc
 
Still not able to get the desired solution..


I have company codes mentioned in the column C as 23,37,4A,51,53,54,55,56,79.


Can I get the aggregate value for all these co codes for a/c 121000..? I need formula here .

When there is only one co code is mentioned I was able to get the values when there is more than one co code I was not able to get it. Please help me
 
Hello Hui


please find the link

https://docs.google.com/spreadsheet/ccc?key=0AoWaaVJwF8R_dHV2MEtLenRpVFNrWTlzSi1OcGNqaXc&hl=en_US


Thanks
 
Whooa


I can't think of a way to collect specific Companies where they are listed as

37,54,55,56,61,62,66,75,79,90,91,2A,4A

in one cell.


This data (Sheet1) appears to be a report from some sort of accounts system

is there any way it can spit out 1 company per row?
 
You must log in or register to reply here.
Back
Top