Find all 6/1/2007 in the Workbook, Sum Corresponding Dollar Figure

[Guest]

Hello all! I am trying to figure out the very simplest way to do something
very complicated. I am looking for a way to sum up all the dollar amounts
that meet two criteria. For instance, I want to sum all the expenses on
6/1/2007, that fell into category LM, and sum all expenses on 6/1/2007, that
fell into category BD, etc. All together, there are 5 categories (BD, VISIT,
LD1, LM, ADJ, & LD2). This is tricky because it requires a double lookup.
Vlookup doesn’t seem to be able to handle the demand. I tried Index/Match
and didn’t get the results I was looking for. Can someone offer a better
suggestion as to how to do this? I am ok with VBA, but still learning and I
don’t feel like I know where to start with this thing. I think it will
require a VBA solution. Finally, the data is stored on multiple sheets,
titled 6-1, 6-2, 6-3 (days in June). Is there a way to find every incidence
of 6/1/2007 in the workbook, then sum the dollar figures that correspond to
LM, BD, etc? I was thinking of taking all values on all sheets, copying them
to one summary sheet, and then working only with that summary sheet for the
lookup, index/match, find, whatever. Does this make sense? I am open to
literally anything. The workbook that I inherited doesn’t work at all so I
am trying to divorce myself from it and start over anyway.

Regards,
Ryan---

 

[Bob Umlas]

=SUMPRODUCT(--(A1:A100=datevalue("6/1/07")),--(B1:B100="BD"),C1:C100)
But if the values are all over the place, you'd probably need to add these
up, like:
=SUMPRODUCT(--(A1:A100=datevalue("6/1/07")),--(B1:B100="BD"),C1:C100)+SUMPRODUCT(--(Sheetx!A1:A100=datevalue("6/1/07")),--(Sheetx!B1:B100="BD"),Sheetx!C1:C100))+SUMPRODUCT(--(Sheety!A1:A100=datevalue("6/1/07")),--(Sheety!B1:B100="BD"),Sheety!C1:C100)
changing the range references as needed, of course.

 

[Guest]

A few things... You could use a pivot tabel with multiple consolidation
ranges to do what you want. All other solutions are pretty much going to
require you to have the summary sheet (to be effective and manageable). If
you have the summary sheet then you could use sumproduct formulas... Here are
some links for you to look at...

http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Guest]

Right on Jim! Using a Pivot Table didn't even occur to me. I tried it with
Date (row) and summed the Total Jobs (data). This will almost do it (why is
it always almost...). However, some incidences of 6/1/2007 fall all over the
workbook. I think I have to copy/paste all data from all sheets (28, 30, 31,
depending on the month) in each workbook and create one summary sheet and
then build the PT there.

I looked at Ron de Bruin's site:
http://www.rondebruin.nl/tips.htm


He offers some AWESOME code for doing many things, but I didn't notice an
easy way to copy data from all sheets in a closed workbook (or open an book
for that matter) into a summary sheet in the active workbook. There is an
example of how to do it for one specific worksheet, but I didn’t see a way to
copy/paste data from all sheets into a single summary sheet. Did I miss
something?

 

[Guest]

Did you try suing multiple consolidation ranges when you set up your povot
table. doing that you can point at a number of sheet...

 

[Guest]

Thanks for the help Bob and Jim. I ended up using a PT and I also used Ron
de Bruin's code from this URL:
http://www.rondebruin.nl/copy2.htm

"Sub Test1()" did the trick. Thanks so much everyone!! Also, thanks to Jim
for underscoring the 'multiple consolidation ranges' option. I have never
tried this before, and I decided not to employ it this time, but I will
certainly try it in the future. If I had used it, I think all the associated
arrays would have caused the PT to be too awkward. I copied the data to one
summary sheet (MergeSheet) and built the PT off of this (and then placed it
in a new worksheet).

Thanks again!!
Ryan---