+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 of 26

IF statements to calculate count

  1. #1
    Forum Contributor
    Join Date
    10-06-2015
    Location
    london
    MS-Off Ver
    2013
    Posts
    149

    IF statements to calculate count

    Hello

    I need some help on getting a count function working using sum product.

    What I want is If I select a quarterly month in the list box on 'quarterly reports', I want it to count the number of inspections as shown on the 'inspections' data sheet for those 3 months.

    I have tried to use this formula but not picking up the months

    =SUMPRODUCT((TEXT(Inspections!$A$2:$A$5,"mmm")=$A$4)*(Inspections!E$2:E$5>=6)*(Inspections!E$2:E$5<=7))

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: IF statements to calculate count

    The values in Inspections column A are actual real dates.
    But A4 is just a text string "Jul Aug Sep"
    So there are no matches.

    Try
    =SUMPRODUCT((ISNUMBER(SEARCH(TEXT(Inspections!A$2:A$5,"mmm"),$A$4)))*(Inspections!E$2:E$5>=6)*(Inspections!E$2:E$5<=7))

  3. #3
    Forum Contributor
    Join Date
    10-06-2015
    Location
    london
    MS-Off Ver
    2013
    Posts
    149

    Re: IF statements to calculate count

    this is not working.

    result for the first table when oct, nov, dec is selected should be 2 and not 0.

  4. #4
    Forum Contributor
    Join Date
    10-06-2015
    Location
    london
    MS-Off Ver
    2013
    Posts
    149

    Re: IF statements to calculate count

    That is working now

  5. #5
    Forum Contributor
    Join Date
    10-06-2015
    Location
    london
    MS-Off Ver
    2013
    Posts
    149

    Re: IF statements to calculate count

    if i wanted to select just the quarterly and only where it counts the east side for each category, do you know how that would go?

  6. #6
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: IF statements to calculate count

    Looks like you posted again while I was responding.
    So the formula did work.

    Not sure I understand what you're asking now about 'east side' ?
    Last edited by Jonmo1; 10-08-2015 at 08:55 AM.

  7. #7
    Forum Contributor
    Join Date
    10-06-2015
    Location
    london
    MS-Off Ver
    2013
    Posts
    149

    Re: IF statements to calculate count

    If you look at the quarterly datasheet and look at the 2nd table 'quartely inspections in the east', currently this counts the inspections based on the month selected against the feature and the grade. What I also want it to do is only count the inspections that show 'east' from the site group in the inspections datasheet

  8. #8
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: IF statements to calculate count

    Sorry, I still can't see what you want.
    Can you speak in exact Sheet Names, column Letters and row #s etc ?

    I don't see anything that says 'quartely inspections in the east'
    I don't see a 2nd table on any sheet..

    Specifically..
    I want to count the cells between rows ? and ?, in column ? on sheet ? that meet criteria ?

  9. #9
    Forum Contributor
    Join Date
    10-06-2015
    Location
    london
    MS-Off Ver
    2013
    Posts
    149

    Re: IF statements to calculate count

    In the spreadsheet in the quarterly reports you have 3 tables.

    In the 2nd table I want it to count the number of inspections against per grade in the east site group against the month that has been selected.

    Attachment 423637

  10. #10
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: IF statements to calculate count

    Please attach EXCEL workbooks, not word documents with pictures.
    We can't work with those.

    And I still don't see that 2nd table in the Excel Workbook you attached in post #1

  11. #11
    Forum Contributor
    Join Date
    10-06-2015
    Location
    london
    MS-Off Ver
    2013
    Posts
    149

    Re: IF statements to calculate count

    Attachment 423638


    see attached

  12. #12
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: IF statements to calculate count

    So column B on the Inspections sheet is the 'Group'
    And you want to filter only the 'East Side Group'

    But there are no values in that column called 'East Side'
    You have 'Rural East', is that what you want included? Anything that has EAST in the text of column B ?

    Would it be better if you had just 1 table on the Quarterly Reports Sheet.
    But you could Filter column B on the Instpections sheet for a particular group and the table on the Quarterly Reports sheet would update accordingly?

  13. #13
    Forum Contributor
    Join Date
    10-06-2015
    Location
    london
    MS-Off Ver
    2013
    Posts
    149

    Re: IF statements to calculate count

    yeah I want it to pick up in that column anything that contains the word 'East' in the group and count and updated the table accordingly in the 'quarterly reports datasheet

  14. #14
    Forum Contributor
    Join Date
    10-06-2015
    Location
    london
    MS-Off Ver
    2013
    Posts
    149

    Re: IF statements to calculate count

    the reason I have the 3 tables on the 'quarterly report datasheet' is because first data counts everything for those months.

    Table 2- will count just the east

    Table 3- will count just for the west

  15. #15
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: IF statements to calculate count

    Quote Originally Posted by colegerald38 View Post
    the reason I have the 3 tables on the 'quarterly report datasheet' is because first data counts everything for those months.
    I get that, what I'm asking is if it would be better for you if you had just 1 table.
    But, when you go the the Inspections sheet and Filter column B for the particular group you want to see, that 1 table automatically adjusts to that?
    It would be better as far as calculation speed goes.
    Having 3 tables doing essentially the same work means 3 times the time needed to do the calculations.

    Anyway, I get what you're after now, give me a few minutes.

+ Reply to Thread
Page 1 of 2 1 2 LastLast

Thread Information

Users Browsing this Thread

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

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