Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: If column B has XX how many times does ZZ appear for Nov?

  1. #1

    If column B has XX how many times does ZZ appear for Nov?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello I am wanting to count the number of times a word appears in a row ONLY if B column contains a particular word eg:

    Names in a column A
    suburb in column b
    Column C - JU has Id codes under dates (daily eg: 1/11/14, 2/11/14 etc)


    In a new tab I want to see results :
    if column b contains the text "testing" then look across that entire row from column for only september dates ) and if the code AL appears calculate this as 1.

    Does that make sence? I have attached a sample as I dont think im explaining myself properly, sorry..

    isabella
    Attached Files Attached Files

  2. #2
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    Excel Version
    Office 365
    =IF(B2="Team 3",COUNTIF(C2:JU2,"=AL"),0)

    Not sure what you are wanting about the Nov part (title)
    Countifs including first line = Nov (and a bit of formatting) should get you that too
    Last edited by WizzardOfOz; 2014-10-23 at 04:25 AM.

  3. #3
    I am thinking he might want an overall summary

    =SUMPRODUCT(($B$2:$B$14="Team 3")*($C$2:$JU$14="AL"))

  4. #4

    count by month

    Hello again,

    That works to count everything:
    =SUMPRODUCT(('HR Operations Leave Register'!B2:B14="Team 3")*('HR Operations Leave Register'!C2:JU14="AL"))

    What I am seeking is to have it count by month as per the spreadsheet sample eg:
    in Jan how many from Team 1 contain "AL"
    in Feb how many from Team 2 contaiin AL
    in Mar how many from team 1 contain AL
    etc

    Isabella

    Quote Originally Posted by WizzardOfOz View Post
    =IF(B2="Team 3",COUNTIF(C2:JU2,"=AL"),0)

    Not sure what you are wanting about the Nov part (title)
    Countifs including first line = Nov (and a bit of formatting) should get you that too

  5. #5
    by november I mean in hte sample attachment I want to break it down by month

  6. #6
    Try this

    =SUMPRODUCT((Text('HR Operations Leave Register'!$C$1:$JU$1,"mmm")="Jan"))*('HR Operations Leave Register'!B2:B14="Team 3")*('HR Operations Leave Register'!C2:JU14="AL"))

    and so on

  7. #7
    Perhaps something like this, using array formulas (means when ENTER you need to press CTRL-SHIFT-ENTER button together)
    Attached Files Attached Files

  8. #8

    Omg

    YOUR AMAZING!!!
    I CANT THANKYOU ENOUGH

    sorry for screaming but i meant it

    Isabella


    Quote Originally Posted by Rizky View Post
    Perhaps something like this, using array formulas (means when ENTER you need to press CTRL-SHIFT-ENTER button together)

  9. #9
    A better way would be to drop the table into Power Query, unpivot the date columns, set the date column header to Date and the data type to Date, the leave column to Leave Type, and load the transformed table into Excel. Then pivot that table, with Team in in rows, Date in columns, and count of Leave Type in Values. Group the dates by Months and Years, add a slicer for Leave Type, and you have the most flexible solution you could imagine.

  10. #10
    Hello Bob,

    im im appreciate your response- it's a foreign language to to me :-), I will however give your instructions a try, if there is anything between these steps however I will fail lol

    iss

    Quote Originally Posted by Bob Phillips View Post
    A better way would be to drop the table into Power Query, unpivot the date columns, set the date column header to Date and the data type to Date, the leave column to Leave Type, and load the transformed table into Excel. Then pivot that table, with Team in in rows, Date in columns, and count of Leave Type in Values. Group the dates by Months and Years, add a slicer for Leave Type, and you have the most flexible solution you could imagine.

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •