COUNTIFS including entire column rather than one single item

2
votes

Goal: To run a COUNTIFS matching Sheet2!A:A to Sheet1!B:B and a completed status from sample below. The goal is for sheet 1 to have training information pulled from a database and then I need to calculate who from that database has completed a list of specific training items (listed on sheet 2).

I have tried to set up a COUNTIFS but can only do it for each individual training item. I would have to set up a long string of these adding up and also include a user condition for the formula to work properly.

COUNTIFS(Sheet1!B:B,**Sheet2!A2**,Sheet1!C:C,"complete")

To save me a lot of time and potential errors, I would like to to run:

COUNTIFS(Sheet1!B:B,**Sheet2!A:A**,Sheet1!C:C,"complete")

However, when I enter the formula it displays an error. I also have tried to utilize pivot tables but it will not allow me to complete a COUNTIFS as a calculated field for the table. I am open to using VBA, but have limited experience in it if that will solve my issue.

Sheet 1:

Name    LO ID/Title Status
user 1    LO 1      complete
user 1    LO 2      complete
user 1    LO 3      registered
user 2    LO 1      complete
user 2    LO 3      complete
user 2    LO 4      complete

Sheet 2:

Cirricula
LO 1
LO 3
LO 4

EDIT: Ideal output on Sheet 2:

User 1 - 1 (because they only have LO1 complete, LO2 is not required in the cirricula, and LO3 is a registered status)

User 2 - 3 (because they have all three items complete required in the cirricula)

3
excelexcel-formulapivot-tablecountifvba
Can you put up a sample of what your output will look like? Will it be a count next to each Cirricula of how many complete statuses there are for that Cirricula? – tospig

3 Answers

1
votes

This involves manual selection (to exclude LO 2) of the LO ID/Title filter (unless set with VBA for example) but seems to give the results required:

SO27027641 example

0
votes

Suppose you have your data like this:

enter image description here

Note that the LO List is in Sheet2. Enter this formula in F2:

=SUMPRODUCT(COUNTIFS(A$2:A$7,E2,B$2:B$7,Sheet2!A$2:A$4,C$2:C$7,"complete"))

Above formula will give you the result you described above.
Not far from what I posted earlier, we just added another criteria range and criteria.
In short, you only need to incorporate SUMPRODUCT
in your COUNTIFS formula when dealing with multiple criteria.

Note: Your formula above is just lacking SUMPRODUCT and the other criteria for the user although I don't recommend using the entire column as criteria since it will hurt Excel's calculation time. Your best bet if your data is dynamic is to use a Dynamic Named Range for the multiple criteria. HTH.

0
votes

On Sheet 2, column B you can use

=SUMPRODUCT((Sheet1!$B$2:$B$7=A2)*(Sheet1!$C$2:$C$7="complete"))

which will give as output

Cirricula   Count complete
LO 1            2
LO 3            1
LO 4            1

Update / Edit

To reference the user, just reference Column A in stead of Column B (or whereever your user column is):

=SUMPRODUCT((Sheet1!$A$2:$A$7=D2)*(Sheet1!$C$2:$C$7="complete"))

Here on Sheet 2 I have User in column D 

Cirricular  count cirricular complete       User    counter user complete
LO 1                2                       user 1          2
LO 2                1                       user 2          3
LO 4                1

Update / Edit 2

To get what you want using formula, I have included a vlookup in Column C to see if the LO ID is in the required list of LO IDs. You can then add this third criteria in the sumproduct formula.

(The vlookup in Column C is =IF(ISERROR(VLOOKUP(B2,$F$2:$F$4,1,FALSE)),"no","yes"))

sumproduct