+ Reply to Thread
Results 1 to 12 of 12

count if with sum product

  1. 10-22-2014, 08:51 PM #1
    lejanco
    lejanco is offline
    Forum Contributor
    Join Date
    05-05-2014
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2013
    Posts
    103

    count if with sum product

    Hello,

    This one has me stumped. I am trying to count the rows in sheet one that have the value in column A in all columns in sheet 1. For example, on the Class on Jan. 2013 sheet (I did this one manually), there are 150 rows which have the value D6 in all columns in sheet 1. I have tried this formula but it does not seem to be working. Any suggestions?

    =COUNTIF(Sheet1!I2:W36278,SUMPRODUCT((Sheet1!I2:W36278='Class of Jan. 2013'!A3)))

    Thanks!

    Laura
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 10-22-2014, 09:59 PM #2
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,938

    Re: count if with sum product

    Try this...

    sorry, that wont work
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  3. 10-22-2014, 10:09 PM #3
    Loganeb
    Loganeb is offline
    Forum Contributor
    Join Date
    10-16-2014
    Location
    Center Line, Michigan, United States
    MS-Off Ver
    MS Office 2013
    Posts
    139

    Re: count if with sum product

    Laura,

    I added a helper column to your data sheet. In the next column, I concantenated all of the previous data columns into one value, then on your counter sheet, I entered a countif function with the range, and the value you're looking for repeated 15 times.

    I have uploaded a sample file (with most of your data deleted, but enough exists to show the process) due to size limits.

    Count if with sum producrt.zip
    Reply With QuoteRegister To Reply
  4. 10-23-2014, 01:55 AM #4
    benishiryo
    benishiryo is offline
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: count if with sum product

    hi Laura. i used Loganeb's file to try since your file is too big. without the helper columns, you may try this array formul in Class of Jan. 2013 sheet. in B2:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

    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
    Reply With QuoteRegister To Reply
  5. 10-23-2014, 01:56 AM #5
    canapone
    canapone is offline
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    count if with sum product (countif per row)

    Hi,

    a possible solution in B2 and below:

    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    There are for sure more elegant ways to solve it.


    ---------------------

    Edit : Hi Benishyrio: I was struggling with FREQUENCY too with no luck. Great formula!


    Cheers
    Last edited by canapone; 10-23-2014 at 01:58 AM.
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.
    Reply With QuoteRegister To Reply
  6. 10-23-2014, 05:41 AM #6
    canapone
    canapone is offline
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: count if with sum product

    Hi,

    a different approach

    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    Cheers
    Last edited by canapone; 10-23-2014 at 05:44 AM.
    Reply With QuoteRegister To Reply
  7. 10-23-2014, 08:57 AM #7
    Vikas_Gautam
    Vikas_Gautam is offline
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Cool Re: count if with sum product

    I think Benshiro has got it right..
    Just extending the ranges in his beautiful formula.. and Removing CTRL + SHIFT + ENTER requirement.. as =15 will do the job..
    Please Login or Register  to view this content.
    Last edited by Vikas_Gautam; 10-23-2014 at 09:00 AM.
    Regards,
    Vikas Gautam
    Excel-buzz.blogspot.com
    Excel is not a matter of Experience, its a matter of Application.
    Say Thanks, Click * Add Reputation
    Reply With QuoteRegister To Reply
  8. 10-23-2014, 09:14 AM #8
    canapone
    canapone is offline
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: count if with sum product

    Hi,

    attached a file with the three kind of approach.

    Hope it helps
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 10-23-2014, 11:34 AM #9
    lejanco
    lejanco is offline
    Forum Contributor
    Join Date
    05-05-2014
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2013
    Posts
    103

    Re: count if with sum product

    Worked! I have never used the Frequency function in excel. If you have time, would you care to explain your formula. Thanks for your time!
    Reply With QuoteRegister To Reply
  10. 10-23-2014, 08:39 PM #10
    Loganeb
    Loganeb is offline
    Forum Contributor
    Join Date
    10-16-2014
    Location
    Center Line, Michigan, United States
    MS-Off Ver
    MS Office 2013
    Posts
    139

    Re: count if with sum product

    Vikas_Gautam,

    I love your solution, it is very elegant. However, I have a question for you. I narrowed the range of your equation to just 2 rows, and your formula returns the wrong result. I don't really understand why it does so. Any thoughts?

    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    Edit: by the way, I was trying to look for "D8". also, when I changed the range of the above formula to I3:W4, it returns 0 instead of 1

    canapone,

    Your use of MMULT is excellent. I never would have thought of that, great job!
    .
    Last edited by Loganeb; 10-23-2014 at 08:57 PM.
    Reply With QuoteRegister To Reply
  11. 10-23-2014, 10:01 PM #11
    Vikas_Gautam
    Vikas_Gautam is offline
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: count if with sum product

    Hi logneb..
    Yes its giving wrong results when changed to 2 rows only, that is because I used Array multiplication to avoid Pressing CTRL + SHIFT + ENTER..
    My non array solution would be correct for more than 1 row reference..

    here is the possible correct arrayed solution..
    Please Login or Register  to view this content.


    Regards,
    Reply With QuoteRegister To Reply
  12. 10-23-2014, 10:07 PM #12
    Loganeb
    Loganeb is offline
    Forum Contributor
    Join Date
    10-16-2014
    Location
    Center Line, Michigan, United States
    MS-Off Ver
    MS Office 2013
    Posts
    139

    Re: count if with sum product

    It does work for anything greater than 2 rows. Thanks!
    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. Count if, sum product, dcount, not sure which i need or how to use it
    By mrproject44 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-08-2013, 11:05 AM
  2. Count if/Sum if/ or Sum product
    By a1b2c3d4e5f6g7 in forum Excel Formulas & Functions
    Replies: 30
    Last Post: 12-04-2012, 03:46 PM
  3. Sum Product or Count If from Another File
    By mycon73 in forum Excel General
    Replies: 9
    Last Post: 10-15-2012, 07:15 AM
  4. totalling product count
    By kenppy in forum Excel General
    Replies: 2
    Last Post: 08-21-2012, 04:31 PM
  5. Count Product
    By impulse03 in forum Excel General
    Replies: 2
    Last Post: 12-03-2011, 01:11 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