+ Reply to Thread
Results 1 to 13 of 13

Only count data from column with a particular heading

  1. #1
    Registered User
    Join Date
    12-10-2013
    Location
    Manchester, UK
    MS-Off Ver
    Excel 2010
    Posts
    61

    Only count data from column with a particular heading

    Hi I have had lots of help with this today, but unfortunately it's still not quite complete. I wonder if someone can help me with a formula to fill in cells B3,4,7,8,12,13,14 using something similar to the formula in cell B17 (all on the DATA ANALYSIS tab)

    It has been suggested to use COUNTIF, but because I will have to paste new spreadsheets onto the info tab sometimes the titles are in different columns depending on how it was originally formatted.

    See here: Blank AnalysisHELP3.xlsx

    Many thanks

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,775

    Re: Only count data from column with a particular heading

    So, are you saying that the Gender won't always be in column D (as the Info sheet has it now, though the formula in B3 is looking at column E) ?

    Pete

  3. #3
    Registered User
    Join Date
    12-10-2013
    Location
    Manchester, UK
    MS-Off Ver
    Excel 2010
    Posts
    61

    Re: Only count data from column with a particular heading

    Yeah sometimes the gender will be further over in E or F depending on which set of data I paste in. Same goes for the other titles in row A, they all swap about

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,775

    Re: Only count data from column with a particular heading

    Well, that doesn't sound like a very consistent design approach on the data layout !!

    Here's how you can get the number of males in B3:

    =SUMPRODUCT(--(INDEX(Info!$A$2:$AO$39,0,MATCH("Gender",Info!$1:$1,0))="M"))

    and the number of Females in B4:

    =SUMPRODUCT(--(INDEX(Info!$A$2:$AO$39,0,MATCH("Gender",Info!$1:$1,0))="F"))

    I've shown in red the things that have changed - can you see how to do this for your other cells?

    Pete

  5. #5
    Registered User
    Join Date
    12-10-2013
    Location
    Manchester, UK
    MS-Off Ver
    Excel 2010
    Posts
    61

    Re: Only count data from column with a particular heading

    Ah yes that's great, it's working! The only problem i'm having now is that for the "number of SEN" in B8, the SEN pupils are indicated by either A S or P and not just by one thing (like M for male) so i'm not sure how to get it to look for any of these letters in the info tab under SEN status

  6. #6
    Registered User
    Join Date
    12-10-2013
    Location
    Manchester, UK
    MS-Off Ver
    Excel 2010
    Posts
    61

    Re: Only count data from column with a particular heading

    PS yep the design is appalling! haha but i'm trying to get it so that my colleagues can paste in their own gathered data into the Info tab so that the Data analysis tab just works it out for them

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,775

    Re: Only count data from column with a particular heading

    You also have N in that column (and a few blanks) - what does that signify?

    Pete

  8. #8
    Registered User
    Join Date
    12-10-2013
    Location
    Manchester, UK
    MS-Off Ver
    Excel 2010
    Posts
    61

    Re: Only count data from column with a particular heading

    The N can be ignored as it means that pupil is not counted as SEN (special educational needs)

  9. #9
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,775

    Re: Only count data from column with a particular heading

    Okay, sometimes it can be easier to write the formula so that it counts the items that are not equal to the non-valid codes, but as you also have blanks in there it makes little difference.

    The formula you currently have is essentially this:

    =COUNTIF(B2:B32,"A")+COUNTIF(B2:B32,"P")+COUNTIF(B2:B32,"S")

    (I've removed the sheet references). It's a bit long-winded, but you could use a similar approach with the longer SP formula, adding three of them together, each looking in the SEN Status column for A P and S in turn. However, here's a more compact version of the COUNTIF formula:

    =SUM(COUNTIF(B2:B32,{"A","P","S"}))

    Note that you can put the 3 variables within curly braces, each separated by a comma - a SUM function is required around the formula to essentially add the 3 different numbers that the COUNTIF produces, though this is not required for the SP version.

    So, change the column header from Gender to SEN Status, and use {"A","P","S"} instead of the "M" in the formula I gave you for the number of males.

    Note that I'm not giving you this "on a plate" - you have to work for it, and then you might learn more from it (as I'm sure you tell your pupils).

    Hope this helps.

    Pete

  10. #10
    Registered User
    Join Date
    12-10-2013
    Location
    Manchester, UK
    MS-Off Ver
    Excel 2010
    Posts
    61

    Re: Only count data from column with a particular heading

    You're an absolute star!

    Going from what you have said I actually ended up using
    =SUMPRODUCT(--(INDEX(Info!$A$2:$AO$39,0,MATCH("SEN Status",Info!$1:$1,0))={"A","S","P"}))

    I wasn't aware of the curly brackets. You have been a really great help!

    I'm sure I will be back mithering again shortly though haha

  11. #11
    Registered User
    Join Date
    12-10-2013
    Location
    Manchester, UK
    MS-Off Ver
    Excel 2010
    Posts
    61

    Re: Only count data from column with a particular heading

    OK my absolute last:

    I tried to type this into B25
    =SUMPRODUCT((INDEX(Info!$A$2:$AO$39,0,MATCH("SEN Status",Info!$1:$1,0))={"S,"A","P"})*((INDEX(Info!$A$2:$AO$39,0,MATCH("KS2-3 Progress4 Y7 Spring 2",Info!$1:$1,0))="Below")))

    to find out how many SEN pupils are performing "below" what they should be, but apparently my formula contains an error...

  12. #12
    Registered User
    Join Date
    12-10-2013
    Location
    Manchester, UK
    MS-Off Ver
    Excel 2010
    Posts
    61

    Re: Only count data from column with a particular heading

    Ah got it, I changed to

    =SUMPRODUCT((INDEX(Info!$A$2:$AO$39,0,MATCH("SEN Status",Info!$1:$1,0))={"A","S","P"})*((INDEX(Info!$A$2:$AO$39,0,MATCH("KS2-3 Progress4 Y7 Spring 2",Info!$1:$1,0))="Above")))

  13. #13
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,775

    Re: Only count data from column with a particular heading

    Well, that's great. I'm sure you realise that the long INDEX...MATCH terms are just selecting the columns to use from the headings. With this longer formula you have two terms separated by an asterisk - essentially, that is equivalent to having two terms which are ANDed together, i.e. both of them have to be satisfied for any given row for it to be counted. I bet you can work out what to do if you have three conditions ...

    If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

    Also, since you are relatively new to the forum, I would like to inform you that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

+ 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. [SOLVED] VBA Count Records Which Match Column Heading
    By hobbiton73 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-20-2014, 05:24 AM
  2. Count if column heading is
    By nd2828 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-09-2012, 03:08 PM
  3. Replies: 4
    Last Post: 10-31-2011, 04:33 PM
  4. In a table produce an value by column heading and row heading
    By naflan in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-27-2005, 01:25 PM
  5. How do I put a Heading on each column of data?
    By new with excel in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 05-09-2005, 03: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

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.07939 seconds
  • Memory Usage 9,203KB
  • Queries Executed 16 (?)
More Information
Template Usage (33):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (13)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (13)postbit_legacy
  • (13)postbit_onlinestatus
  • (13)postbit_wrapper
  • (4)showthread_bookmarksite
  • (5)showthread_similarthreadbit
  • (1)showthread_similarthreads
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (39):
  • ./vbseo.php
  • ./env.php
  • ./vbseo/includes/functions_vbseo.php
  • ./vbseo/includes/functions_vbseo_pre.php
  • ./vbseo/includes/functions_vbseo_url.php
  • ./vbseo/includes/functions_vbseo_createurl.php
  • ./vbseo/includes/functions_vbseo_db.php
  • ./vbseo/includes/functions_vbseo_vb.php
  • ./vbseo/includes/functions_vbseo_seo.php
  • ./vbseo/includes/functions_vbseo_misc.php
  • ./vbseo/includes/functions_vbseo_crr.php
  • ./vbseo/includes/functions_vbseo_cache.php
  • ./vbseo/includes/functions_vbseo_hook.php
  • ./vbseo/includes/functions_vbseo_startup.php
  • ./includes/config.php
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/functions.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/functions_cforum.php
  • ./includes/functions_facebook.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php
  • ./includes/functions_notice.php 

Hooks Called (48):
  • init_startup
  • friendlyurl_resolve_class
  • database_pre_fetch_array
  • database_post_fetch_array
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • cache_templates
  • template_register_var
  • parse_templates
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • memberaction_dropdown
  • tag_fetchbit_complete
  • showthread_similarthread_query
  • showthread_similarthreadbit
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1