SOLVED

Summing values and cross-referencing second tab for criteria. Please Help!

Copper Contributor

Hello all,

This is my first time posting here and I am in need of some guidance.  I have an excel file with two tabs containing different sales information on the same underlying transaction IDs.  The goal is to find the sum of transactions that fit criteria on one of the tabs and also fit criteria of a variable on the other tab.  I've explained the goal in detail on the first tab in the data file which can be downloaded from my Google Drive here: Data 

 

I've been thinking of some sort of SUMIFS formula but cant seem to get around the parameter of the unique transaction ID values and the fact that they will change with each new daily generation of the reports in this file.

 

Thank you in advance for any feedback or ideas on how to go about this.

-Jack

2 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

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
1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

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.

View solution in original post