Forum Discussion
Summing values and cross-referencing second tab for criteria. Please Help!
Hi jeriley
first of all, thanks for the really good and precise explanation in the sample file. This is realy a tricky one.
I came to a solution, but this requires Excel from Microsoft 365/Office 365, because it uses the new FILTER function. If you have it, you can try the following formula:
=SUM(SUMIFS(Transactions!$H$2:$H$1000;Transactions!$E$2:$E$1000;"succeeded";Transactions!$A$2:$A$1000;VLOOKUP(Transactions!$A$2:$A$1000;FILTER('Daily Sales'!$A$2:$A$1000;('Daily Sales'!$D$2:$D$1000="Transient Dockage")+('Daily Sales'!$D$2:$D$1000="Other"));1;FALSE)))
I added also the sample file where I put the formula in B13 of the explanation sheet.
Hi jeriley
first of all, thanks for the really good and precise explanation in the sample file. This is realy a tricky one.
I came to a solution, but this requires Excel from Microsoft 365/Office 365, because it uses the new FILTER function. If you have it, you can try the following formula:
=SUM(SUMIFS(Transactions!$H$2:$H$1000;Transactions!$E$2:$E$1000;"succeeded";Transactions!$A$2:$A$1000;VLOOKUP(Transactions!$A$2:$A$1000;FILTER('Daily Sales'!$A$2:$A$1000;('Daily Sales'!$D$2:$D$1000="Transient Dockage")+('Daily Sales'!$D$2:$D$1000="Other"));1;FALSE)))
I added also the sample file where I put the formula in B13 of the explanation sheet.
- Thank you so much! This is amazing. I honestly don't really understand the new FILTER function but will look into it now. Thanks again and I hope you enjoyed this challenge as well.
-Jack
