Forum Discussion

JakeOliver's avatar
JakeOliver
Copper Contributor
Oct 24, 2024

COUNTIFS Automatically pick up cell

I have built a worksheet using countifs to counts the number of transits ships make in and out of the port with 3 criteria. As we are monitoring across a 12 month period for compliance the workbook i...
Excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Nov 12, 2024

    As variant it could be

    =IF( [@[Date & Time]] = "", "-",
 IF( ( MOD([@[Date & Time]],1) >= 8/24 ) *
     ( MOD([@[Date & Time]],1) < 20/24 ),
     "Day", "Night")
 )

    (see in December tab), but it depends what do you consider as Day and what as Night.

    Here MOD([@[Date & Time]],1) extracts time part of datetime, 8/24 means 8am, 20/24 means 8pm. Better not to hardcode above but move into parameters (named cells).

    Before inserting the formula delete values in all cells of the column B within the table, with that formula will be applied automatically to all cells here. Includes the situation if you resize the table.

Harun24HR's avatar
Harun24HR
Bronze Contributor

JakeOliver You can use cell reference as criteria of COUNTIFS() function. Suppose you have names from A2 to downwards. Then use the following formula and drag down.

=COUNTIFS(June!$C$2:$C$638,"Night",June!$F$2:$F$638,A2,June!$K$2:$K$638,"Inbound",June!$M$2:$M$638,"Tory")

 It would be nice if you share a sample workbook with sample input data and then show your desired output. There are too smart function in latest Excel version those can automate your calculations.

JakeOliver's avatar
JakeOliver
Copper Contributor
Oct 28, 2024

Harun24HR - Thanks for the response. 

 

Here are some screenshots from the workbook. 

 

This the input sheet for January. We are counting across as 12 month period. Each month is a separate sheet. 

 

 

PEC Counts sheet

 

Desired output is to count the number of transits each year according to the criteria and then compare if that person had met the requirement. 

 

Nice to have is graphs for each person for presenting the data. 

 

  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Oct 29, 2024

    JakeOliver 

    If you rename tables as short month names, formula could be

    =COUNTIFS(
  INDIRECT(C$3 & "[[Day / Night]:[Day / Night]]"),IF(RIGHT($A4,4) = "Day)", "Day", "Night" ),
  INDIRECT(C$3 & "[[Master]:[Master]]"), XLOOKUP("Master",$A$1:$A4,$B$1:$B4,,,-1),
  INDIRECT(C$3 & "[[In/Out]:[In/Out]]"), IF( ISNUMBER(SEARCH("Inbound", $A4)), "Inbound", "Outbound" ),
  INDIRECT(C$3 & "[[Tory Ch./ N. Ent]:[Tory Ch./ N. Ent]]"), IF(LEFT($A4,4)="Tory", "Tory", "N Ent" )
)

    You may copy/paste it to all cells

    • JakeOliver's avatar
      JakeOliver
      Copper Contributor
      Oct 29, 2024

      SergeiBaklan 

       

      Thanks for this. Looks great. 

       

      I have tried to bring the formula over to the next worksheet "Pilot Counts", certain columns have worked others have gone to #REF!. Can you have a look and give me a hand fixing this? 

       

      Currently the pilots only operate via the N Ent. However have the option for Tory is good for future proofing.

       

      Cheers.

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP
        Oct 30, 2024

        JakeOliver 

        #REF! if not 3-letters month names are used.

         

        You shall to rename tables or rename column names:

        Or alternatively use in formula LEFT(C$3,3) instead of C$3

        =COUNTIFS(
  INDIRECT( LEFT(C$3,3) & "[[Day / Night]:[Day / Night]]"),IF(RIGHT($A4,4) = "Day)", "Day", "Night" ),
  INDIRECT( LEFT(C$3,3) & "[[Pilot]:[Pilot]]"), XLOOKUP("Pilot",$A$1:$A4,$B$1:$B4,,,-1),
  INDIRECT( LEFT(C$3,3) & "[[In/Out]:[In/Out]]"), IF( ISNUMBER(SEARCH("Inbound", $A4)), "Inbound", "Outbound" ),
  INDIRECT( LEFT(C$3,3) & "[[Tory Ch./ N. Ent]:[Tory Ch./ N. Ent]]"), IF(LEFT($A4,4)="Tory", "Tory", "N Ent" )
)

Resources