Results 1 to 5 of 5

Thread: Get number of rows where month equals X AND where other criteria match

  1. #1

    Get number of rows where month equals X AND where other criteria match



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

    Hi there, I have searched this forum (and other Excel resources) for answers to my question but haven't been able to solve the problem yet, apologies if it transpires that I was being stupid and reading things wrong but I hope someone can help me anyway.

    Firstly, I am using Excel 2007, SP3. And I don't know if this is a setting but my version appears to be using column numbers instead of letters. So the first cell is "R1C1" instead of "A1". But now I've got my head around how it works (especially for conditional formatting) I quite like it this way.

    Anyway, my problem... I have a workbook that has three worksheets.

    Worksheet 1 (called "Error Log") is a list of issues. Each row is a separate issue.

    Worksheet 2 (called "Drop-downs") is just data for the drop-down menus in sheet 1.

    Worksheet 3 (called "Stats") is going to be statistics about sheet 1, but this is where I'm having trouble.

    In sheet 3 column 2 has dates formatted as MMMM so it just shows the month. For each month listed in sheet 3 I want to extract all of the following data from sheet 1:

    1) The total number of rows in sheet 1 where month is the same as the current line in sheet 3 (dates in sheet 1 are in column 1).

    2) The total number of rows in sheet 1 where month is the same as the current line in sheet 3 AND where company responsible is my company (company at fault is listed in column 3 of sheet 1).

    3) The total number of rows in sheet 1 where month is the same as the current line in sheet 3 AND where company responsible is NOT my company.

    4) The total number of rows in sheet 1 where month is the same as the current line in sheet 3 AND where company responsible is my company AND where team responsible equals my team (team at fault is listed in column 4 for sheet 1).

    Please can someone help? if anything is not clear (I'm aware this might be confusing) then please ask.

    Thank you.

  2. #2
    Conjurer royUK's Avatar
    Join Date
    Mar 2011
    Location
    Derbyshire, UK
    Posts
    155
    Articles
    0
    Excel Version
    most versions
    To change your settings read this

    For your summary I think a PivotTable would do this, I'm not sure why you want the number of Rows
    Hope that helps

    Roy

  3. #3
    Thanks for the replies guys, but with a bit more experimentation I have managed to solve my own problem. For the benefit of anybody else searching in future who may find this thread, here are the formulas I used:

    For the total number of rows per month:
    =SUMPRODUCT(--(MONTH('Error Log'!R2C1:R9997C1)=MONTH(RC[-1])),--(YEAR('Error Log'!R2C1:R9997C1)=YEAR(RC[-1])))
    Number of issues for the month attributable to my company:
    =SUMPRODUCT(--(MONTH('Error Log'!R2C1:R9997C1)=MONTH(RC[-4])),--(YEAR('Error Log'!R2C1:R9997C1)=YEAR(RC[-4])),--('Error Log'!R2C3:R9997C3='Drop-downs'!R2C2)+('Error Log'!R2C3:R9997C3='Drop-downs'!R4C2))
    Number of issues for the month NOT attributable to my company
    =SUMPRODUCT(--(MONTH('Error Log'!R2C1:R9997C1)=MONTH(RC[-7])),--(YEAR('Error Log'!R2C1:R9997C1)=YEAR(RC[-7])),--('Error Log'!R2C3:R9997C3<>'Drop-downs'!R2C2),--('Error Log'!R2C3:R9997C3<>'Drop-downs'!R4C2))
    Number of issues for the month attributable to my team
    =SUMPRODUCT(--(MONTH('Error Log'!R2C1:R9997C1)=MONTH(RC[-10])),--(YEAR('Error Log'!R2C1:R9997C1)=YEAR(RC[-10])),--('Error Log'!R2C4:R9997C4='Drop-downs'!R2C3))
    Oh, and I actually quite like the R1C1 style of cell naming, makes conditional formatting much easier for me, i just mentioned it as it would have been relevant for formulas / answers.

    Cheers all.
    Last edited by torshen; 2013-07-01 at 03:51 PM.

  4. #4
    Conjurer royUK's Avatar
    Join Date
    Mar 2011
    Location
    Derbyshire, UK
    Posts
    155
    Articles
    0
    Excel Version
    most versions
    Thanks for posting for other users, but subject to seeing the data layout I would have thought a PivotTable would be a better option
    Hope that helps

    Roy

  5. #5
    Ditto royUK

Posting Permissions

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