Forum Discussion
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 is working well.
One of the criteria is counting the Captain's name (1 of 41 names). Is there a way to automate the formula to pick up the name rather than typing it 96 times for each person.
This is the COUNTIFS formula - The Name in this case is Starkey =COUNTIFS(June!$C$2:$C$638,"Night",June!$F$2:$F$638,"Starkey",June!$K$2:$K$638,"Inbound",June!$M$2:$M$638,"Tory")
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.
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.
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.
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
