sum column between multiple files
I have 2 databases I created in Excel. Database #1 is used by a person to scan in transactions daily. Each scan registers the date of the scan in column A, column B shows the number of cartons and column C shows the number of lines.
The second database is the master database that shows information from the first database and 2 others. The information gets uploaded each time the "master" database is opened.
My problem - on the master database I want the daily information from Database #1 column B to be summed on the Master databse but I can't get the formula to work. I tried SUMIF but it is not doing what I need.
Column A Column B Column C
Date Cartons Lines
03/02/13 21 42
03/02/13 19 32
03/03/13 33 58
03/04/13 10 8
So in the master database for 03/02/13 I want the formula to look at database #1, search column A for the date of 03/02/13 and sum column B if there is a QTy for that date.
How do I do the formula?
Assuming (for ease of
Assuming (for ease of example) that you are using named ranges
> DbDates is all the dates in your database
> Querydate is the cell you enter your date of interest
> dbcartons is all the cartons in your database
then
=SUMIF{(DbDates,QueryDate,DbCartons)}
will sum all the cartons for the given query date
&
=SUMIF{(DbDates,QueryDate,DbLines)}
will sum all the lines for the given query date
NOTE
Don't forget the curly brackets as it's an array formula