+ Reply to Thread
Results 1 to 17 of 17

Sumproduct OR adding criteria to Countif, Counta and Average

  1. 02-20-2010, 12:16 PM #1
    Wkruger
    Wkruger is offline
    Registered User
    Join Date
    02-19-2010
    Location
    Laurel, MD
    MS-Off Ver
    Excel 2003
    Posts
    22

    Wink Sumproduct OR adding criteria to Countif, Counta and Average

    I trying to convert the following formula (I, II, III) and add criteria if Name, Date to the count values of “c”

    CURRENT FORMULA

    I: “=COUNTIF(C2:C3100,"<=6")” versley (B) and today to last 7 days (A)
    II: “=COUNTA(C2:C3100)” versley (B) and today to last 7 days (A)
    III: “=AVERAGE(C2:C3100)” versley (B) and today to last 7 days (A)


    Add additional criteria

    Date (A ): (A2:A3100<=TODAY())*(CA:A3100>=TODAY()-7))
    Name (B): B2:B3100="vensley"
    Count (C): Less than conditions


    Sorry if I was not clear enough and appreciate any help

    Date Received Team Grade
    15-Jan-10 Susan 9
    22-Jan-10 William 10
    27-Jan-10 Mike 10
    11-Feb-10 Mike 7
    6-Jan-10 vensley 0
    7-Jan-10 vensley 6
    15-Jan-10 vensley 3
    15-Jan-10 vensley 8



    Wm…
    Attached Files Attached Files
    Last edited by Wkruger; 02-24-2010 at 02:13 AM.
    Reply With QuoteRegister To Reply
  2. 02-20-2010, 01:45 PM #2
    Palmetto
    Palmetto is offline
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Sumproduct OR adding criteria to Countif, Counta and Average

    Welcome to the Forum Wkruger,

    To get the most precise answer in the quickest way, it will be best if you upload a sample workbook meeting the following criteria:

    1. It EXACTLY duplicates the structure in your real workbook, AND
    2. It contains representative but non-sensitive dummy data, AND
    3. It contains clear examples of the expected results manually created

    To upload a sample workbook, edit your first post, click Go Advanced and scroll down the page until you see the button "Manage Attachment" - should be intuitive from there.
    Palmetto

    Do you know . . . ?

    You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.
    Reply With QuoteRegister To Reply
  3. 02-21-2010, 11:00 AM #3
    jeffreybrown
    jeffreybrown is offline
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Sumproduct OR adding criteria to Countif, Counta and Average

    Does not comply with Rule #8

    Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!
    http://www.mrexcel.com/forum/showthr...=1#post2222118

    Please read
    http://www.excelguru.ca/node/7
    Reply With QuoteRegister To Reply
  4. 02-21-2010, 12:33 PM #4
    Wkruger
    Wkruger is offline
    Registered User
    Join Date
    02-19-2010
    Location
    Laurel, MD
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Sumproduct OR adding criteria to Countif, Counta and Average

    I have the same question on MrExcel. I was asked to clarify my request and it was suggested that I use Index and Match. Just not sure how or when. Please review if you wish. http://www.mrexcel.com/forum/showthr...=1#post2222161
    Reply With QuoteRegister To Reply
  5. 02-22-2010, 07:40 PM #5
    Wkruger
    Wkruger is offline
    Registered User
    Join Date
    02-19-2010
    Location
    Laurel, MD
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Sumproduct OR adding criteria to Countif, Counta and Average

    Any suggestions please
    Reply With QuoteRegister To Reply
  6. 02-22-2010, 08:26 PM #6
    jeffreybrown
    jeffreybrown is offline
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Sumproduct OR adding criteria to Countif, Counta and Average

    If you are looking for a suggestion Aladin gave you one...

    Did you try it?

    http://www.mrexcel.com/forum/showpos...77&postcount=6
    Reply With QuoteRegister To Reply
  7. 02-23-2010, 01:32 PM #7
    Wkruger
    Wkruger is offline
    Registered User
    Join Date
    02-19-2010
    Location
    Laurel, MD
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Sumproduct OR adding criteria to Countif, Counta and Average

    I did try and will double check again. Thanks for replying
    Reply With QuoteRegister To Reply
  8. 02-23-2010, 02:15 PM #8
    Wkruger
    Wkruger is offline
    Registered User
    Join Date
    02-19-2010
    Location
    Laurel, MD
    MS-Off Ver
    Excel 2003
    Posts
    22

    Cool Re: Sumproduct OR adding criteria to Countif, Counta and Average

    I tried again and getting the zero as results. I included a copy of spreadsheet with suggested outcomes. Im sure I made some kind of typo.


    =SUM(IF(NPS!$B$2:$B$3100=F2,IF(NPS!$A$2:$A$3100>=F1,IF(NPS!$A$2:$A$3100<=F1-7,IF(NPS!$C$2:$C$3100<=F4,1)))))
    =SUM(IF(NPS!$B$2:$B$3100=F2,IF(NPS!$A$2:$A$3100>=F1,IF(NPS!$A$2:$A$3100<=F1-7,IF(NPS!$C$2:$C$3100<=F3,1)))))
    =SUM(IF(NPS!$B$2:$B$3100=F2,IF(NPS!$A$2:$A$3100>=F1,IF(NPS!$A$2:$A$3100<=F1-7,1))))
    =AVERAGE(IF(NPS!$B$2:$B$3100=F2,IF(NPS!$A$2:$A$3100>=F1,IF(NPS!$A$2:$A$3100<=F1-7,NPS!$C$2:$C$3100))))

    Thank you,
    William
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 02-23-2010, 03:05 PM #9
    WHER
    WHER is offline
    Valued Forum Contributor
    Join Date
    07-29-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2003/Excel 2010
    Posts
    534

    Re: Sumproduct OR adding criteria to Countif, Counta and Average

    Probably a typo. Check the ">=" and "<=" in the formula's
    =SUM(IF(NPS!$B$2:$B$3100=F2,IF(NPS!$A$2:$A$3100>=F1,IF(NPS!$A$2:$A$3100<=F1-7,IF(NPS!$C$2:$C$3100<=F4,1)))))
    A date cannot be both bigger than today and smaller than 7 days ago.
    Reply With QuoteRegister To Reply
  10. 02-23-2010, 04:53 PM #10
    Wkruger
    Wkruger is offline
    Registered User
    Join Date
    02-19-2010
    Location
    Laurel, MD
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Sumproduct OR adding criteria to Countif, Counta and Average

    I check the signs and believe they are correct. It’s something else. Any other suggestions? The spreadsheet is attached.
    Reply With QuoteRegister To Reply
  11. 02-23-2010, 07:45 PM #11
    Wkruger
    Wkruger is offline
    Registered User
    Join Date
    02-19-2010
    Location
    Laurel, MD
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Sumproduct OR adding criteria to Countif, Counta and Average

    I reviewed again and I did see a few errors - sorry and ty. I also moved the data so its on the same page.

    =SUM(IF($B$25:$B$3100=F2,IF($A$25:$A$3100>=F1,IF($A$25:$A$3100<=F1-7,IF($C$25:$C$3100<=F3,1)))))
    =SUM(IF($B$25:$B$3100=F2,IF($A$25:$A$3100>=F1,IF($A$25:$A$3100<=F1-7,1))))
    =AVERAGE(IF($B$25:$B$3100=F2,IF($A$2:$A$3100>=F1,IF($A$25:$A$3100<=F1-7,$C$25:$C$3100))))


    Current error: #Value!

    Wm...
    Reply With QuoteRegister To Reply
  12. 02-23-2010, 07:49 PM #12
    jeffreybrown
    jeffreybrown is offline
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Sumproduct OR adding criteria to Countif, Counta and Average

    Let me first say you probably shouldn't keep posting on both links. Aladin is helping you out on MrExcel, but I'm sure he does not want to waste his time if the answer has been provided on this site.

    For the formula, did you confirm the entry with Ctrl+Shift+Enter?
    Reply With QuoteRegister To Reply
  13. 02-23-2010, 08:28 PM #13
    Wkruger
    Wkruger is offline
    Registered User
    Join Date
    02-19-2010
    Location
    Laurel, MD
    MS-Off Ver
    Excel 2003
    Posts
    22

    Unhappy Re: Sumproduct OR adding criteria to Countif, Counta and Average

    I did do control Ctrl+Shift+Enter and I must be doing something else incorrectly or do you suggest a differ variation of the formula? I been looking at the formula and dont see the error.

    I appreciate your help as well as Aladin I just been working on this for several days. Once I have a solution I will let forum know. I attached a current version as I cant see what IM doing wrong or my Ctrl+Shift+Enter command is not working - thanks again
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  14. 02-23-2010, 08:45 PM #14
    jeffreybrown
    jeffreybrown is offline
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Sumproduct OR adding criteria to Countif, Counta and Average

    Please explain in words what you are trying to accomplish.

    A25:C76 is that the table you need evaluated against the parameters in F1:F3.

    What are your expected results?
    Reply With QuoteRegister To Reply
  15. 02-23-2010, 08:53 PM #15
    jeffreybrown
    jeffreybrown is offline
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Sumproduct OR adding criteria to Countif, Counta and Average

    Somebody on MrExcel offered some help...

    http://www.mrexcel.com/forum/showpos...3&postcount=12
    Reply With QuoteRegister To Reply
  16. 02-24-2010, 02:07 AM #16
    Wkruger
    Wkruger is offline
    Registered User
    Join Date
    02-19-2010
    Location
    Laurel, MD
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Sumproduct OR adding criteria to Countif, Counta and Average

    Thank you for the heads up on the solution. Its works. Im closing this post and appreciate your help!

    FYI on the solution...

    I would have done it like this (Shift+Ctrl+Enter):

    1. Number of occurences where the date in column A is in the last 7 days, Name in column B is "versley", and the value in column C is less than or equal to 6.
    =COUNT(IF($A$2:$A$3100<=TODAY(),IF($A$2:$A$3100>(TODAY()-7),IF($B$2:$B$3100="versley",IF($C$2:$C$3100<=6,$C$2:$C$3100)))))


    2. Number of occurences where the date in column A is in the last 7 days, Name in column B is "versley".
    =COUNT(IF($A$2:$A$3100<=TODAY(),IF($A$2:$A$3100>(TODAY()-7),IF($B$2:$B$3100="versley",$C$2:$C$3100))))


    3. Average of the values in column C where the date in column A is in the last 7 days, Name in column B is "versley".
    =AVERAGE(IF($A$2:$A$3100<=TODAY(),IF($A$2:$A$3100>(TODAY()-7),IF($B$2:$B$3100="versley",$C$2:$C$3100))))
    __________________
    Regards,
    Sachin Acharya
    Reply With QuoteRegister To Reply
  17. 02-24-2010, 03:32 AM #17
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Sumproduct OR adding criteria to Countif, Counta and Average

    Sachin, we're obviously glad you got a resolution but FWIW going forward should you continue to x-post you will find people soon lose interest in your threads or avoid altogether for fear of wasting their time.
    (and trust me when I say that you don't want the likes of those who partook on these particular threads losing interest in you / your questions)
    My Recommended Reading:

    Volatility
    Sumproduct & Arrays
    Pivot Intro
    Email from XL - VBA & Outlook VBA
    Function Dictionary & Function Translations
    Dynamic Named Ranges
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1