# Thread: SUMIF Help (Excel 2000)

1. ## SUMIF Help (Excel 2000)

Hello!!!

I have an SUMIF statement that I would like to add additional criteria into.

=(SUMIF(EDate,"<="&SDate,J_Ph_Calls)-SUMIF(EDate,"<"&FOW,J_Ph_Calls))

I have one more piece of criteria that I would like to fit in here, but am (as always) having a hard time. Is it possible, and if so where would I add
IF(EAssoc=\$A4)
into this formula?

Mucho Thanks!

2. ## Re: SUMIF Help (Excel 2000)

It's time for a different type of formula:

=SUMPRODUCT((EDate<=SDate)*(Edate>=FOW)*(EAssoc=\$A 4)*J_Ph_Calls)

You can add as many conditions as you like

3. ## Re: SUMIF Help (Excel 2000)

Fabulous, oh man that never rests!

I did have to re-define my named ranges from \$A:\$A to \$A\$##:\$A\$#### to get the formula to work. Why is that?

4. ## Re: SUMIF Help (Excel 2000)

According to MS in Description of the limitations for working with arrays in Excel 2000, Excel 2002, and Excel 2003 it is implied that it is "by design"

Though why A2:A65536 or A1:A65535 would be allowed and not A1:A65536 is beyond me... <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15> I guess 1 cell can make a lot of difference...

Steve

5. ## Re: SUMIF Help (Excel 2000)

Entire rows are OK though. Here some thoughts regarding the issue:

http://tinyurl.com/ahysg

6. ## Re: SUMIF Help (Excel 2000)

I don't know the exact "why", but array and array-type formulas only work with finite ranges, not with entire rows or columns.

7. ## Re: SUMIF Help (Excel 2000)

Thanks for the clarification and the information...

Steve

#### Posting Permissions

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