+ Reply to Thread
Page 2 of 2 FirstFirst 1 2
Results 16 to 26 of 26

IF statements to calculate count

  1. #16
    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

    This will add the criteria of column B containing the word East

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

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

    Re: IF statements to calculate count

    thanks

    If wanted to go with the one table, are you able to do the count for the west, east and the whole district where it is displayed separately?

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

    Re: IF statements to calculate count

    I had set it up at 3 purely so it is easier to see the displayed information

  4. #19
    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

    Here's how you can do it with just 1 table.

    I added a helper column to the inspections sheet column AJ
    In AJ2 and filled down
    =SUBTOTAL(103,[@[Sample Date]])
    It returns a 1 if the row is visible, 0 if it's hidden.
    You can hide that column.

    Then the basic formula in the Quarterly Reports sheet, B5 is
    =SUMPRODUCT((ISNUMBER(SEARCH(TEXT(Inspections!$A$2:$A$5,"mmm"),$A$4)))*(Inspections!E$2:E$5>=6)*(Inspections!E$2:E$5<=7)*Inspections!$AJ$2:$AJ$5)

    When column B on the Inspecitons sheet is NOT filtered at all, everything shows in the table.
    When you filter column B on the Inspecitons sheet and select only the group(s) you want to see, the table adjusts accordingly.

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

    Re: IF statements to calculate count

    are you able to send me the excel worksheet with the formula inputted?

  6. #21
    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

    It's too big, I don't know how you managed to zip it to less than 1MB
    But I can't, it's still over 3MB when I zip it.

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

    Re: IF statements to calculate count

    i just saved it and then right clicked and selected 'send to compressed zip folder'

  8. #23
    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

    EFcolegerald.xlsb

    I deleted all the hidden sheets, and removed macros
    That cut the size way down.

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

    Re: IF statements to calculate count

    Great thank you for all your all

  10. #25
    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

    You're welcome.

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

    Re: IF statements to calculate count

    Are you any good at creating charts?

+ Reply to Thread
Page 2 of 2 FirstFirst 1 2

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