Results 1 to 4 of 4

Thread: Formula help

  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    A few formula questions, as it's late and my brain has switched off....

    =SUMPRODUCT(('Daily Files'!$A$2:$A$20000="H")*('Daily Files'!$H$2:$H$20000="SOL"),('Daily Files'!$I$2:$I$20000))

    This gives me the sum, how do I get the count?

    =COUNTIF('Payment Files'!E:E,"H")
    =SUMIF('Payment Files'!E:E,"H",'Payment Files'!I:I)

    In both of these, I need to add a further condition to only count/sum the figures in I that are positive?

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    To get the count of rows meeting both conditions:

    =SUMPRODUCT(('Daily Files'!$A$2:$A$20000="H")*('Daily Files'!$H$2:$H$20000="SOL"))

    To count the number of rows where column E contains "H" and column I contains a positive number:

    =SUMPRODUCT(('Payment Files'!E1:E20000="H")*('Payment Files'!I1:I20000>0))

    And to sum the numbers in column I with these conditions:

    =SUMPRODUCT(('Payment Files'!E1:E20000="H")*('Payment Files'!I1:I20000>0),'Payment Files'!I1:I20000)

    You can't use SUMPRODUCT with entire columns.

  3. #3
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This should do it:

    =SUMPRODUCT(('Daily Files'!$A$2:$A$20000="H")*('Daily Files'!$H$2:$H$20000="SOL")*1)

  4. #4

Posting Permissions

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