COUNTIFS formula

[rakeshtendulkar]

Dear All,
I have created basic Staff Attendance Sheet in Excel. It is on Monthly basis where I can put "P" - Present, "A" - Absent etc... and I can get each person monthly summary of Attendance at extreme right..
Then I have created "TOTAL" worksheet in which I have got total year summary of each person. And side of that I have created one table, in which at cell "O3" I have Data validation where I can select each person name by pulling down list.
How I can use COUNTIFS formula to get full year (from Jan to Dec) attendance by keeping criteria as cell "O3 - person name & M5 to M12 - as another criteria for each month?
Because I want to use this table as a Data for creating graph for each person.

OR I can use some another formula?

thanks in advance & regards
Rakesh Tendulkar

 

[Asheesh]

Hi Rakesh,

use the below in cell O5, then drag it down and across

INDEX(INDIRECT("'"&O$4&"'!"&"$AG$6:$AN$49"),MATCH($O$3,INDIRECT("'"&O$4&"'!"&"$A$6:$A$49"),0),MATCH($M5,INDIRECT("'"&O$4&"'!"&"$AG$5:$AN$5"),0))

 

[rakeshtendulkar]

Dear Asheesh,
thanks a lot for your quick help. It's working very nicely.. but may be I forgot to put one more point regarding my format sheet. each person got 2 rows which gives me details for 1st half of work and 2nd half of work after lunch time. So if a person goes half day today. worksheet should calculate working days, absent etc. accordingly.
Also if you can just elaborate how this formula works, which will help me to understand the same.
thanks in advance and regards,
rakesh

 

[Asheesh]

Hi Rakesh,

Use the below formula in Cell O5 then drag it down and across

SUMPRODUCT(INDIRECT("'"&O$4&"'!"&"$AG$6:$AN$49"),(INDIRECT("'"&O$4&"'!"&"$A$6:$A$49")=$O$3)*(INDIRECT("'"&O$4&"'!"&"$AG$5:$AN$5")=$M5))

To Learn Indirect Function - http://spreadsheetpro.net/how-to-ma...a-worksheet-in-excel-and-google-spreadsheets/

To Learn Index Match Match Combination - https://www.ablebits.com/office-addins-blog/2014/08/13/excel-index-match-function-vlookup/

To Learn SUMPRODUCT - http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/

 

[Khalid NGO]

Hi Rakesh,

Use the below formula in Cell O5 then drag it down and across

SUMPRODUCT(INDIRECT("'"&O$4&"'!"&"$AG$6:$AN$49"),(INDIRECT("'"&O$4&"'!"&"$A$6:$A$49")=$O$3)*(INDIRECT("'"&O$4&"'!"&"$AG$5:$AN$5")=$M5))

Hi Asheesh,
I think we can make it shorter, since we have used hard coded references, INDIRECT is not changing the cell range:

=SUMPRODUCT((INDIRECT(O$4&"!AG5:AN5")=$M5)*(INDIRECT(O$4&"!A6:A49")=$O$3)*(INDIRECT(O$4&"!AG6:AN49")))

Regards,

 

[Asheesh]

@Khalid NGO

Absolutely right...dint strike my mind...