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]

isrdebie wrote:
> 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/...ample.xls.html

Let me know if I messed it up.

[Franz Verga]

isrdebie wrote:
> 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/...ample.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]

isrdebie wrote:
> 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/...ample.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]

isrdebie wrote:
> 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/...ified.xls.html


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

[Franz Verga]

Franz Verga wrote:
> isrdebie wrote:
>> 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/...ified.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