+ Reply to Thread
Results 1 to 5 of 5

SumProduct vs Count if formula

  1. 01-20-2014, 04:37 PM #1
    daisiedawn
    daisiedawn is offline
    Registered User
    Join Date
    01-20-2014
    Location
    Oklahoma City, Oklahoma
    MS-Off Ver
    Excel 2010
    Posts
    21

    SumProduct vs Count if formula

    Hello,

    I've been reviewing the forum threads and almost found the answer I need, however I have one more item I need help with.

    I need to calculate the number of "valid" cells in column K if it's in the same row as Employee A. This formula is working splendidly. I'm using Excel 2010.

    =SUMPRODUCT(--('2014'!B2:B301=A23),--('2014'!K2:K301="Valid"))

    I need to add in to look per calendar month so I don't have to adjust the rows to review monthly.


    Column B = Employee Name
    Column C =Date Received
    Column K = Valid


    Any help you can offer would be greatly appreciated.
    Thanks,
    Monica
    Last edited by daisiedawn; 01-20-2014 at 04:45 PM.
    Reply With QuoteRegister To Reply
  2. 01-20-2014, 04:40 PM #2
    Jonmo1
    Jonmo1 is offline
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: SumProduct vs Count if formula

    Try

    =SUMPRODUCT(--('2014'!B2:B301=A23),--('2014'!K2:K301="Valid"),--(TEXT('2014'!C2:C301,"mmmyyyy")="Jan2014"))
    Reply With QuoteRegister To Reply
  3. 01-20-2014, 04:47 PM #3
    daisiedawn
    daisiedawn is offline
    Registered User
    Join Date
    01-20-2014
    Location
    Oklahoma City, Oklahoma
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: SumProduct vs Count if formula

    Thank you Jonmo1. All my dates are in MM/DD/YYYY format. So I think the mmmyyy format isn't working as expected.
    Reply With QuoteRegister To Reply
  4. 01-20-2014, 04:51 PM #4
    daisiedawn
    daisiedawn is offline
    Registered User
    Join Date
    01-20-2014
    Location
    Oklahoma City, Oklahoma
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: SumProduct vs Count if formula

    Nevermind. I think that worked. Helps if I abbreviate correctly. Sorry.

    I really appreciate your assistance. Thank you so very Much!
    Reply With QuoteRegister To Reply
  5. 01-20-2014, 04:53 PM #5
    Jonmo1
    Jonmo1 is offline
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: SumProduct vs Count if formula

    Glad to help, thanks for the feedback.
    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)

Similar Threads

  1. [SOLVED] I need COUNTIFS or SUMPRODUCT formula to count ONLY Non-Consecutive values
    By justinbelkin in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-19-2013, 03:24 PM
  2. Sumproduct formula to count the latest run of 1's problem
    By Philb1 in forum Excel General
    Replies: 4
    Last Post: 02-14-2012, 02:47 AM
  3. sumproduct formula to count keyword in cells for a given month
    By philllipoosis in forum Excel General
    Replies: 1
    Last Post: 01-13-2011, 02:28 PM
  4. Using a SumProduct Count to find a SumProduct Total?
    By XL021710 in forum Excel General
    Replies: 3
    Last Post: 02-18-2010, 08:31 AM
  5. SUMPRODUCT Formula to Count Row of data Below Matched Criteria
    By Sam via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-02-2005, 10:06 PM

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