Formula

[GDawg]

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

 

[Jay Petrulis]

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

 

[eliW]

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

 

[GDawg]

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

 

[Jay Petrulis]

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

 

[GDawg]

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

 

[Aladin Akyurek]

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)

Aladin

 

[Jay Petrulis]

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)))

Sorry about all the bad responses from me.

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).

Adjust your ranges to suit.

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

 

[GDawg]

Worked great! Thanks Jay and Aladin.

Gdawg