CODEWITHSUNDEEP
excelexcel-formulaexcel-2007vba
IndyMom83
IndyMom83

Reputation: 153

Countif formula with multiple criteria

In my workbook, I have a 4 spreadsheets - Data, Amy, Betty, Connie. Data has the following columns: 

  Column A   Column B                  Column C             Column D

Row 3 Employee Total tasks last 30 days. Total tasks last 7 days. Date/Time Last task Assigned Row 4 Amy
Row 5 Betty
Row 6 Connie

In A1 - worker with oldest task Date/Time Assigned. Cell B1 contains the answer to cell A1 by providing the employees name.

I enter the data in the individual workers spreadsheets (Amy, Betty, Connie) and my Data worksheet provides the summary. On my data worksheet, I have formulas that count the number of tasks within the last 7 days and last 30 days by using the date/time assigned column (Column E)in the individual worksheets. Currently, if someone is out of the office, on their individual worksheet, I enter OUT in the task column (Column B) and the date/time that they will return in the date/time assigned column (Column E)so it will skip them for B1 on my Data worksheet. I need a formula that would not count the date/time assigned if they were OUT as a task, when calculating in columns B and C in the data worksheet.

Here is the formula that I have currently for Column B (total tasks last 30 days)

{=COUNTIF(INDIRECT("'"&A4&"'!"&"E1:E1000"),">="&(NOW()-30))}

The formula that I have for cell B1 is: {=INDEX(A4:A14,MATCH(MIN(E4:E14),E4:E14,0))}

Any help would greatly be appreciated.

Upvotes: 0

Views: 884

Answers (2)

IndyMom83
IndyMom83

Reputation: 153

I figured it out:

=COUNTIFS(Amy!E2:E1000,">="&(NOW()-30),Amy!A2:A1000,"<>out")

Upvotes: 1

natancodes
natancodes

Reputation: 1008

Like suggested in the comments, the COUNTIFS formula is what you want to use. Information about its usage can be found here.

Adding the condition for excluding the "OUT" values would make the formula look like this:

=COUNTIFS(INDIRECT(""&A4&"!"&"E1:E1000"),">="&(NOW()-30),INDIRECT(""&A4&"!"&"B1:B1000"),"<>OUT")

Upvotes: 0

Related Questions