DATE RANGES and other functions, formulas

[isrdebie]

Using 2003 for awhile now, but running into a brick wall. Have a client
with an inventory workbook with 156+ worksheets. Each sheet represents
an artist with columns of: title & medium, size, received, date,
price, date sold, invoice, paid, comments, balance, type. There is a
summary sheet we created to show how much is in inventory for each
artist. That was easy enough. Just ran a total on each worksheet,
then had the summary sheet read that worksheet and cell.

Now we need to figure out year to date sales for each artist. I have
been searching the Internet and my Excel 2003 for dummies book, but no
luck on how to pull up that info. The summary sheet shows only the
artist name, amount in inventory, and year to date sales. I want to be
able to tell the summary sheet to read the artist sheet for a date sold
range, then pick up the paid amount. One problem is that inventory
that came in two or three years ago might not sell until this year, so
there are varying dates in the database. I think I need to SUMIF or
COUNTIF my PAID column (which is a dollar amount) and the IF would be
if the range of DATE SOLD column is between 1/1/06 and 12/31/06, but I
can't quite make it over the hurdle. If there is anyone out there who
can help, I would greatly appreciate it.

 

[Franz Verga]

Using 2003 for awhile now, but running into a brick wall. Have a
client with an inventory workbook with 156+ worksheets. Each sheet
represents an artist with columns of: title & medium, size,
received, date, price, date sold, invoice, paid, comments, balance,
type. There is a summary sheet we created to show how much is in
inventory for each artist. That was easy enough. Just ran a total
on each worksheet, then had the summary sheet read that worksheet and
cell.

Now we need to figure out year to date sales for each artist. I have
been searching the Internet and my Excel 2003 for dummies book, but no
luck on how to pull up that info. The summary sheet shows only the
artist name, amount in inventory, and year to date sales. I want to
be able to tell the summary sheet to read the artist sheet for a date
sold range, then pick up the paid amount. One problem is that
inventory that came in two or three years ago might not sell until
this year, so there are varying dates in the database. I think I
need to SUMIF or COUNTIF my PAID column (which is a dollar amount)
and the IF would be if the range of DATE SOLD column is between
1/1/06 and 12/31/06, but I can't quite make it over the hurdle. If
there is anyone out there who can help, I would greatly appreciate it.

I think you could use a SUMPRODUCT function, but it's quite impossible
without a sample of data... It could be very simple if you could upload a
sample file to www.savefile.com or www.rapidshare.de...


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[isrdebie]

Franz,

Thanks. I hope you are still out there. I uploaded to rapidshare.com
Here's the link. Thanks again. I'll start checking into SUMPRODUCT.

http://rapidshare.de/files/30165244/Excel_Data_Sample.xls.html

Let me know if I messed it up

 

[Franz Verga]

Franz,

Thanks. I hope you are still out there. I uploaded to
rapidshare.com. Here's the link. Thanks again. I'll start checking
into SUMPRODUCT.

http://rapidshare.de/files/30165244/Excel_Data_Sample.xls.html

Let me know if I messed it up.

I think there could be some problem because in the column named Date Sold
you don't have only dates, but also RTA, i.e. text that is impossible to use
in calculation...

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[isrdebie]

Franz,

Thanks for your help. I did find a solution. While looking at
SUMPRODUCT, I also looked over SUMIF and conditional sum. I am using
the conditional sum add-in, with a SUMIF formula. I created a
condition to sum the price sold column if the DATERANGE is >= 1/1/06.
Worked like a charm. Thanks. What's the weather like in Italy. Is it
morning there. It's 8:00 pm here and just about time to wind down for
the night. Thanks again.

 

[Franz Verga]

Franz,

Thanks. I hope you are still out there. I uploaded to
rapidshare.com. Here's the link. Thanks again. I'll start checking
into SUMPRODUCT.

http://rapidshare.de/files/30165244/Excel_Data_Sample.xls.html

Let me know if I messed it up.

Maybe this is what you are looking for:

=SUM((IF(G2:G27="RTA",H2:H27,G2:G27)>=L2)*(IF(G2:G27="RTA",H2:H27,G2:G27)<=M2)*I2:I27)

the formula must be array entered, i.e. you have to press CTRL + SHIFT +
ENTER instead of just ENTER...

In L2 you have the starting date you want to evaluate while M2 is the last
date of your interval.

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Franz Verga]

Franz,

Thanks for your help. I did find a solution. While looking at
SUMPRODUCT, I also looked over SUMIF and conditional sum. I am using
the conditional sum add-in, with a SUMIF formula. I created a
condition to sum the price sold column if the DATERANGE is >= 1/1/06.
Worked like a charm. Thanks. What's the weather like in Italy. Is
it morning there. It's 8:00 pm here and just about time to wind down
for the night. Thanks again.

The weather is quite hot. Here is 5.37 am and I'm quite going to sleep...

Here is my complete solution with SUMPRODUCT:

http://rapidshare.de/files/30170929/Excel_Data_Sample_-_modified.xls.html


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Franz Verga]

The weather is quite hot. Here is 5.37 am and I'm quite going to
sleep...
Here is my complete solution with SUMPRODUCT:

http://rapidshare.de/files/30170929/Excel_Data_Sample_-_modified.xls.html

Just for precision...

It's not a real SUMPRODUCT, but I used the SUM function array entered,
because I needed an IF function inside and with IF you always need to array
enter (if you are working with arrays...) even if you are using SUMPRODUCT,
so I preferred to use SUM instead...

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy