RichardElk
New Member
- Joined
- Feb 7, 2014
- Messages
- 8
Hi,
I have some data that looks like this:
(Sheet1)
Crew Day1
CR1 5
CR1 2
CR2 3
CR5 1
(Sheet2 Array)
CrwMkp B C D
CR1 1 3 1
CR2 2 1 1
CR3 5 0 0
CR4 6 2 3
CR5 0 1 0
What I am trying to do is to look through Sheet1 and for every non-zero instance in column called Day1 count the number of of a particular resource used. So for example if i was looking for the resource count for resource B on Day 1 i would look for a result of 4 (Two occurances of CR1 = 2 B's, 1 instance of CR2 = 2B's)
Here is an example of the formula i am using modified for the example above:
={Sum(If(Day1<>0,1,0)*Index({Sheet2Array},Match({Crew},{CrwMkp}),2))}
I think my issue is stems from using an array as a lookup_value in the match function but im not sure of a workaround for this.
Any help would be very much appreciated.
I have some data that looks like this:
(Sheet1)
Crew Day1
CR1 5
CR1 2
CR2 3
CR5 1
(Sheet2 Array)
CrwMkp B C D
CR1 1 3 1
CR2 2 1 1
CR3 5 0 0
CR4 6 2 3
CR5 0 1 0
What I am trying to do is to look through Sheet1 and for every non-zero instance in column called Day1 count the number of of a particular resource used. So for example if i was looking for the resource count for resource B on Day 1 i would look for a result of 4 (Two occurances of CR1 = 2 B's, 1 instance of CR2 = 2B's)
Here is an example of the formula i am using modified for the example above:
={Sum(If(Day1<>0,1,0)*Index({Sheet2Array},Match({Crew},{CrwMkp}),2))}
I think my issue is stems from using an array as a lookup_value in the match function but im not sure of a workaround for this.
Any help would be very much appreciated.
