+ Reply to Thread
Results 1 to 3 of 3

Combine two formulas to count (countif and productsum)

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    01-17-2011
    Location
    amsterdam, Netherlands
    MS-Off Ver
    Excel 2007
    Posts
    189

    Combine two formulas to count (countif and productsum)

    I have a short question

    In cell I3 to M5 are three option to count if the seperate values in column G minus I2 are bigger or equal to 30.

    But I have to make this number also dependent of two other variables in column E and F.

    So I Have two formulas that I have to combine a countifs and productsum

    So in total three things have to be done:
    Date received has to be bigger then cell I2, and Aspect needs to be yes (column F), and count of these cells if the seperate values in column G minus I2 are bigger or equal to 30.
    See attached excel file

    Hope someone can help me!

    Thanks a lot
    Attached Files Attached Files
    Last edited by keis386; 07-24-2012 at 03:37 AM.

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,156

    Re: Combine two formulas to count (countif and productsum)

    hi keis386. a little confused. the 3rd criteria should be I2 - the dates in Column G must be >= 30 right? Cause most of the dates are earlier than I2.
    And i still couldn't get 11. This is what my formula does & it ties with what i filtered:
    Date Received > 16th Jul 2012
    Aspect = "Yes"
    16th Jul 2012 - Trade Date >= 30

    =SUMPRODUCT(($E$2:$E$75>I$2)*($F$2:$F$75="yes")*($I$2-(Table1[TRADE DATE])>=30))

    i get 8. could you clarify what's wrong?

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Forum Contributor
    Join Date
    01-17-2011
    Location
    amsterdam, Netherlands
    MS-Off Ver
    Excel 2007
    Posts
    189

    Re: Combine two formulas to count (countif and productsum)

    I had to look very long, but your formula is good and works!
    I has a few minor mistakes thats why it was not working.

    Thanks a lot!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1