Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Formula

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #3
    Board Regular eliW's Avatar
    Join Date
    Mar 2002
    Posts
    1,919
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #5
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #6
    New Member
    Join Date
    Apr 2002
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,060
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    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

  8. #8
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  9. #9
    New Member
    Join Date
    Apr 2002
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Worked great! Thanks Jay and Aladin.

    Gdawg

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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