+ Reply to Thread
Results 1 to 11 of 11

How to use the Countif

  1. #1
    Registered User
    Join Date
    07-11-2017
    Location
    newcastle, england
    MS-Off Ver
    2013
    Posts
    10

    How to use the Countif

    Hi guys, I hope you can help.

    I have a spreadsheet with a list of names in column A1. in column B1 has a T-shirt quantity, and C1 has the size of the T-Shirt (S, M, L, XL, 2XL)

    Is there a quick way of calculating how many of each size I need? The list in question is about 2500 rows down and has other garments within the list so it wouldn't be as simple as just sorting by size and highlighting the quantities. Ideally what I want is a summary on sheet 2 showing me the totals - if that makes sense?

    Thanks

  2. #2
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: How to use the Countif

    if you have in summary sheet in A2:A10 the names, and in B2:H2 the sizes then in B2 and drag right and down =sumproduct((sheet1!$a$1:A$50=$A2)*(sheet1!$c$1:c$50=b$1),sheet1!$b$1:b$50)
    Click the * to say thanks.

  3. #3
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How to use the Countif

    If this is a flat and normalised two dimensional range then normally a Pivot Table is the quickest and most efficient way to analyse this sort of thing.

    However please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

    Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

    To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Registered User
    Join Date
    07-11-2017
    Location
    newcastle, england
    MS-Off Ver
    2013
    Posts
    10

    Re: How to use the Countif

    Thanks, I think I have uploaded an example sheet now.

  5. #5
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: How to use the Countif

    Unfortunately, you haven't

  6. #6
    Registered User
    Join Date
    07-11-2017
    Location
    newcastle, england
    MS-Off Ver
    2013
    Posts
    10

    Re: How to use the Countif

    Uploaded now
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    07-11-2017
    Location
    newcastle, england
    MS-Off Ver
    2013
    Posts
    10

    Re: How to use the Countif

    Thanks, can see it now.

  8. #8
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: How to use the Countif

    Here is a array formula entered with Ctrl+Shift+Enter. I added the numbers in A2:A15 and the sizes in B1:F1 in another sheet:
    =SUM(IF((CLOTHING!$C$4:$AC$17=B$1)*(CLOTHING!$A$4:$A$17=$A2)*ISNUMBER(CLOTHING!$B$4:$AB$17),CLOTHING!$B$4:$AB$17,0))

  9. #9
    Registered User
    Join Date
    07-11-2017
    Location
    newcastle, england
    MS-Off Ver
    2013
    Posts
    10

    Re: How to use the Countif

    Thanks Paul, Sorry i'm struggling to follow what you suggested lol. I have attached an example of one of the sheets im working on if that helps

  10. #10
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: How to use the Countif

    See attached.
    Attached Files Attached Files

  11. #11
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How to use the Countif

    Here's the Pivot Table approach I mentioned in #3
    I reformatted your data into the layout required by a PT. It's essentially the same as yours but the data is arranged by unique fields. It means it has more rows than your but there's no wasted blank space.

    I also added a date column since sometimes where records record dates it's useful to be able to anlayse by date / month / year etc.
    I also added some SLicers to the PT so that you can easily filter the PT
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Adding a COUNTIF function to an existing IF AND COUNTIF Formula...
    By Ourkid123uk in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-24-2018, 10:09 AM
  2. Replies: 8
    Last Post: 05-27-2017, 07:04 AM
  3. Faster if/countif formula for flagging uniques - countif too slow
    By Speshul in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-19-2015, 01:44 PM
  4. Combining a Date Range COUNTIF and a general COUNTIF
    By jacobtom in forum Excel General
    Replies: 1
    Last Post: 09-15-2011, 05:06 PM
  5. Adding a CountIF to a formula that is already Countif
    By Cmorgan in forum Excel General
    Replies: 4
    Last Post: 06-01-2011, 09:34 AM
  6. [SOLVED] COUNTIF or not to COUNTIF on a range in another sheet
    By Ellie in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-15-2005, 05:06 PM
  7. Countif w/ Multiple Criteria-How do I use countif
    By Patrick_KC in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-09-2005, 05:05 PM

Tags for this Thread

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