1. ## Date problem (2003)

Hi

I am using the following array formula which works.
{=SUM((date1=E\$2)*(code1=\$C28)*units1)}
I have dates in row 2

How can I adjust the formula so that date1 => D\$2 and date1 <E\$2.

Regards

2. ## Re: Date problem (2003)

=SUM((date1>=D\$2)*(date1<E\$2)*(code1=\$C28)*units1)

(again as an array formula, i.e. confirm with Ctrl+Shift+Enter)

3. ## Re: Date problem (2003)

Either:
{=SUM((date1>=D\$2)*(date1<E\$2)*(code1=\$C28)*units1 )}
or the non-array formula:
=SUMPRODUCT((date1>=D\$2)*(date1<E\$2)*(code1=\$C28)* units1)

4. ## Re: Date problem (2003)

Thank you for both replies.

I would never have thought of that, I was trying AND and IF AND configurations.

Thank you again

Regards

5. ## Re: Date problem (2003)

The idea behind this kind of SUM / SUMPRODUCT formulas is that you can add as many conditions as you need in the form of extra factors in the multiplication. No IF and AND needed - you didn't have IF and AND in the original formula either.

6. ## Re: Date problem (2003)

An alternative solution is this array formula:

=SUM(IF((date1>=\$D2)*(date1<E\$2)*(code1=\$C28),unit s1))

This version can be adapted for other aggregate functions: you can replace SUM with functions such as AVERAGE, MIN, MAX, MEDIAN or MODE.

7. ## Re: Date problem (2003)

Hans

Thank you for the lesson, I did not realize that the original formula is in fact also an if formula (in disguise) since it checks for certain conditions and all that happened now is an extra condition added.

Now that I know that I can see a lot of applications for it in my work.

Thank you again very much.

Regards

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•