Thanks:  0
Likes:  0

1. Using either an array formula or a SUMPRODUCT formula works fine for accumulating values in col B depending on values in col A unless either col is interrupted by text. Is there a way around this?

Thanks,

Gdawg

2. On 2002-04-29 08:00, GDawg wrote:
Using either an array formula or a SUMPRODUCT formula works fine for accumulating values in col B depending on values in col A unless either col is interrupted by text. Is there a way around this?

Thanks,

Gdawg
Hi,

Include two ISNUMBER(Range) arguments in your formula.

=SUMPRODUCT((A1:A5>20)*(ISNUMBER(A1:A5))*(B1:B5)*(ISNUMBER(B1:B5)))

Untested, but I believe this will work.

Bye,
Jay

3. This also works:
=SUMIF(A1:A8,">20",B1:B8)
Eli

4. I modfied the formula to be =SUMPRODUCT((ISNUMBER(B1:B15>1))*(MONTH(ISNUMBER(A1:A15)=1))*(ISNUMBER(C1:C15)))and it gets no value, which it was before if no text is inserted and it still gets #VALUE if text interrupts any one of the three cols. Did I use ISNUMBER incorrectly?

Thanks,
GDawg

5. On 2002-04-29 08:28, GDawg wrote:
I modfied the formula to be =SUMPRODUCT((ISNUMBER(B1:B15>1))*(MONTH(ISNUMBER(A1:A15)=1))*(ISNUMBER(C1:C15)))and it gets no value, which it was before if no text is inserted and it still gets #VALUE if text interrupts any one of the three cols. Did I use ISNUMBER incorrectly?

Thanks,
GDawg
Separate the actual criteria from the ISNUMBER tests.

=SUMPRODUCT((Month(A1:A15)=1)*(B1:B15>1)*(C1:C15)*(ISNUMBER(A1:A15))*(ISNUMBER(B1:B15))*(ISNUMBER(C1:C15)))

I believe this is total overkill on the range, but it should do what you want.

Bye,
Jay

6. Still doesn't work completely. Column A has dates 01/15/02 etc., Column B has invoice numbers and Column C, dollar values. I literally copied your formula and plugged it into the worksheet. It worked with no text. It's weird but if i enter a text character into Col A or C i get #VALUE however text in Col B donesn't cause a problem. And just deleting the text from A and C isn't satisfactory - i have to clear contents to get rid of #VALUE. I'll continue to experiment - i appreciate your input and if you have anyother ideas please let me know.

Thanks,

GDawg

7. On 2002-04-29 10:15, GDawg wrote:
Still doesn't work completely. Column A has dates 01/15/02 etc., Column B has invoice numbers and Column C, dollar values. I literally copied your formula and plugged it into the worksheet. It worked with no text. It's weird but if i enter a text character into Col A or C i get #VALUE however text in Col B donesn't cause a problem. And just deleting the text from A and C isn't satisfactory - i have to clear contents to get rid of #VALUE. I'll continue to experiment - i appreciate your input and if you have anyother ideas please let me know.

Thanks,

GDawg
Given the state of your data, I'd suggest using the following array-formula:

{=SUM(IF((ISNUMBER(A1:A4)+ISNUMBER(B1:B4)+ISNUMBER(C1:C4))=3,(A1:A4=\$I\$1)*(B1:B4>\$I\$2)*C1:C4))}

where I1 houses the date criterion and I2 a numeric value.

If your data range is too big, you could trade off space against time:

In D2 enter and copy down as far as needed:

=IF((ISNUMBER(A1)+ISNUMBER(B1)+ISNUMBER(C1))=3,(A1=\$I\$1)*(B1>\$I\$2)*C1,0)

and apply to D:

=SUM(D:D)

8. Hi,

Another way...

Array-enter

=SUM(IF(ISNUMBER(A1:A15),IF(MONTH(A1:A15)=1,ISNUMBER(B1:B15)*(B1:B15>1)*IF(ISNUMBER(C1:C15),C1:C15,0),0)))

Your tests, MONTH(A1:A15)=1 and B1:B15>1, can reference cells, as Aladin suggests (it is a good habit to *never* have constants in your formulas).

Bye,
Jay

EDIT: Text in invoice column always > any number, so formula needed to be adjusted. Aladin's formula is shorter.

[ This Message was edited by: Jay Petrulis on 2002-04-29 11:51 ]

9. Worked great! Thanks Jay and Aladin.

Gdawg

## User Tag List

#### Posting Permissions

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