Countif formula with multiple criteria

0
votes

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.

2
excelexcel-formulaexcel-2007vba
there is the countifS formula that allows multiple ifs – Siphor
How would I add it to my countif formula {=COUNTIF(INDIRECT("'"&A4&"'!"&"E1:E1000"),">="&(NOW()-30))} – IndyMom83
A1 is just text explaining what is in B1 – IndyMom83
This is referencing a sheet directly, but does only count if both conditions are met. =COUNTIFS(Sheet2!E:E,">="&NOW()-30,Sheet2!B:B,"<>OUT") – natancodes

2 Answers

1
votes

I figured it out:

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

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")