Formula to use to match data from one sheet to another then calculate the total

Christine Lazzaro

Hi All!!!

I'm terrible at formulas and trying to learn! I need your help PLEASE!

I have a Master sheet where everyone places orders, it contains the an account number to be charged to and total for each order, in a separate sheet I have all of the account numbers. In the second sheet I need to create a formula that for this account number go look at the master sheet and find all order with the same account number and tell me the total cost of all of the orders.

I would greatly appreciate any guidance!

Matt Lynn-PCG

@Christine Lazzaro Use need to use a sum(collect()) or a Sumif() formula. Something like:
=Sum(collect({cross sheet ref for the $ column},{Cross sheet ref for the account number column},AccountNumber@row))

or
=sumif({Cross sheet ref for the account number column} , AccountNumber@row , {cross sheet ref for the $ column})

Christine Lazzaro

Thank you, that works!!!

We now have another criteria to add to it stating if the two account numbers match and xxx column is ="Approved" then calculate all for that account number.

Would I simply add AND to it or should it be something else?

=sumif({Cross sheet ref for the account number column} , AccountNumber@row , AND {Cross shet ref} Approved",{cross sheet ref for the $ column})

Paul Newcome

Try this:

=SUMIFS({$$ Column}, {Account Number}, @cell = AccountNumber@row, {XXX Column}, @cell = "Approved")

Christine Lazzaro

Thanks Paul @Paulnewcome, I get an error of "unparsetable" this is what is looks like

=SUMIFS({SOURCEpromasterWinningAmount}, {SOURCEpromasterAccountNumber,[OS ACCOUNT CODE]@row,{SOURCEpromasterProcureApproval},"Approved"})

Paul Newcome

You are missing a closing curly bracket } at the end of the second {Cross Sheet Reference}. It looks like it got put in after "Approved" so taking that one out of there and moving it to the previously mentioned spot should do the trick.