Best Formula to use to COUNTIFS based on several criteria.

Jay Duque

I'm trying to come up with a formula that will return a number based on several criteria. I have a separate sheet that I have a key of allotted days off for each area (Vacation, Personal, Summer Friday, and so on). On that separate sheet, I have an available days based on the Key, minus the formula that i'm trying to figure out

This is what I'm trying to do:

COUNTIFS (NAME), (NUMBER OF DAYS), (VACATION IS CHECKED), (APPROVAL), "Approved")

I technically want it to provide me the NUMBER OF DAYS from that cell, based on the following, the name of that person, if it's a VACATION DAY (Personal, summer friday, and so on), and the approval is "Approved".

Does that make sense?

Time off request sheet:

Key Available Dates Sheet:

Jeff Reisman

I think you would want to use a SUMIFS instead of COUNTIFS. COUNTIFS would count cells, SUMIFS would add up the values in cells. Utilize the "reference another sheet" functionality when you create your SUMIFS in order to reference the columns in the Time off Request sheet.

=SUMIFS({Time Off Request - Days}, {Time Off Request - Name}, NAME@row, {Time Off Request - Vacation}, 1, {Time Off Request - Jay Approval}, "Approved")

Something like the above but of course insert your own references, and you may need to reference the NAME column for the parent row.