Reconcile sheets with calendar dates in rows vs dates in columns.

Hello everyone,

I am trying to reconcile two extensive data sheets by quantities on a certain date, to find any diferences per person. I have bee trying a Sumif on the 1st sheet 5th column without success. I have limited experience with excel so would greatly appreciate any suggestions or advice on how to solve this problem.

The objective is to find differences in quantities of fruit on each day. So I was thinking of maybe a SUMIFS on the 5th column of sheet 1 that gives me the difference in quantities.

There are two caviats, the second sheet has the date in columns and there is a location, so the same type of fruit can be spread in different locations.

The location is irrelevant, I just need the formula to sum the total of fruits per city and person on a given day on sheet 2 and compare it to sheet 1.

Has an example John on the 1st of Jan has 6 oranges on sheet 1 but on sheet 2 he only has a total of 5 oranges if you sum the oranges in both NY and LA. So ideally the formula would flag a difference of 1 between the 2 sheets.

Please let me know If my explanation was not very clear.

Many thanks.

Please note: to mark a thread as solved, please select Thread Tools from the menu link above and mark this thread as SOLVED. I will change it for you this time.

No, I did it, but you'll know for next time!

Originally Posted by jtakw

You're welcome, happy to help, thanks for the rep.

Welcome to the forum.

Thanks, shame I only found this forum now

I'm afraid I have to reopen the thread though, excel can't handle the SUMPRODUCT function due to the size of the source files, so I started working on a SUMIFs function

So I came up with =SUMIFS($J$2:$J$9,$G$2:$G$9,B2,$H$2:$H$9,C2) which works for day 1, the problem now is applying the sum_range to several columns, on the attached example it would be columns K and L.

I was trying to find the solution on google, and the more practical solution seems to be to apply an array {} to the sum_range.

Any suggestions or advice on how to apply the array to my sum_range would be greatly appreciated.

And just a refresher for new readers I am trying to reconcile the quantities of sheet 2 (dates in columns and 2 different locations) with sheet 1 (dates in rows and no location)

Thanks