CODEWITHSUNDEEP
excelindexingmatchvlookup
osomanden
osomanden

Reputation: 643

excel Find multiple conditional results

I am looking into doing som statistics on users task stati.

Data Sheet1:

    A   B   C   D
1   NAMEID  NAME    TASK    STATUS
2   1002    Lars    Sorting Done
3   1003    Erik    Sorting Done
4   1002    Lars    Stacking    Awaiting
5   1004    Brian   Sorting Awaiting

Helper Stati Sheet2: A list of the available Stati with an numerical id attached

    A   B
1   STATUS  STATUSID
2   Startup 11
3   Awaiting    22
4   Done    33

Stat Sheet3:

    A   B   C   D   E
1   NAMEID  NAME    Startup Awaiting    Done
2   1002    Lars    0   1   1
3   1003    Erik    0   0   1
4   1004    Brian   0   1   0

I need to find, per Stat:NAMEID, if there is more than 0(zero) stati in the data list equal to Helper:STATUS - if present, print 1, if not present, print 0.

anyone?

Upvotes: 0

Views: 43

Answers (2)

Andreas
Andreas

Reputation: 23968

It seems you are Danish so because of that I will give you the Danish functions and comment with the English.

' In sheet 3 C2: 
  =TÆL.HVISER(Sheet1!A:A;A2;Sheet1!D:D;$C$1) ' =countifs(Sheet1!A:A;A2;Sheet1!D:D;$C$1)

' in sheet 3 D2:
  =TÆL.HVISER(Sheet1!A:A;A2;Sheet1!D:D;$D$1) ' =countifs(Sheet1!A:A;A2;Sheet1!D:D;$D$1)

' in sheet 3 E2:
  =TÆL.HVISER(Sheet1!A:A;A2;Sheet1!D:D;$E$1) ' =countifs(Sheet1!A:A;A2;Sheet1!D:D;$E$1)

and fill down to suit.

I placed it all on one sheet but it will work on multiple sheets too. enter image description here

EDIT: forgot to add sheet in the formula

Upvotes: 1

Brian
Brian

Reputation: 2108

Here is a formula that should work for you:

=COUNTIFS(Sheet1!$A$2:$A$5,Sheet3!$A2,Sheet1!$D$2:$D$5,Sheet3!C$1)

Put it in cell C2. Then drag down and across.

Upvotes: 1

Related Questions