I have few POs. Each PO has PO items. PO items are typically same for all POs.
I have a summary in XLS with PO nos, po items (only one po item for a row corresponding to the po no) and charges.
I need to total charges for a given PO and given PO item. Please refer attached XLS file - sheet 1 and sheet 2.
Please help.
Thanks and regards,
bdmistri
=SUMPRODUCT((Sheet1!B3:B24=Sheet2!D6)*(Sheet1!C3:C24=Sheet2!C9))
Put that formula in cell L9 on sheet 2. Now whatever you put in D6 and C9 will be summed for you.
BTW, this would be easier to keep track of mentally if you gave NAMES to the data in the columns on Sheet1. Highlight all of B from B3 down and name it PONUM, and C3 down name it POITEM, then you can use them in formula like so:
=SUMPRODUCT((PONUM=Sheet2!D6)*(POITEM=Sheet2!C9))
Last edited by JBeaucaire; 10-31-2008 at 07:22 PM.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
I got the concept. The given formula required the following change for the result I was looking for.
=SUMPRODUCT((Sheet1!B3:B24=Sheet1!K18)*(Sheet1!C3:C24=Sheet1!J21),Sheet1!D3:D24)
Now I know - what to do if I need to restrict the total for a date.
Thanks.. it works..
Your adjustment above appears to provide the $ value for the same range. Is that what you meant? You mention date, but don't specify if you're asking another question or not.
All good?
Oh, to keep the syntax clean, I would suggest the following format:
=SUMPRODUCT((Sheet1!B3:B24=Sheet1!K18)*(Sheet1!C3:C24=Sheet1!J21)*(Sheet1!D3:D24))
Last edited by JBeaucaire; 10-31-2008 at 08:02 PM.
All good. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks