# Two references and two answers

1. ## Two references and two answers

Hi all, I'm trying to figure this out and would be greatful if someone knows how to do this:

I have to col B with a date and col C with a name - col D and E contain an action taken by col C. I want to reference the date and the name with these two actions and count how many times Name has taken certain actions on which date. Attached a sample sheet to show what I mean.

Cheers  Register To Reply

2. ## Re: Two references and two answers

hi Jafs. something like this in J5 maybe?
=COUNTIFS(\$B\$2:\$B\$37,\$I5,\$D\$2:\$D\$37,J\$4,\$C\$2:\$C\$37,"Mike")

not sure how your name varies. if you have just the first name, this would work too:
=COUNTIFS(\$B\$2:\$B\$37,\$I5,\$D\$2:\$D\$37,J\$4,\$C\$2:\$C\$37,LEFT(\$J\$3,FIND(" ",\$J\$3)-1))

the 2 formulas above can be copied to K5. for L5, change the reference to range Column E instead:
=COUNTIFS(\$B\$2:\$B\$37,\$I5,\$E\$2:\$E\$37,L\$4,\$C\$2:\$C\$37,LEFT(\$J\$3,FIND(" ",\$J\$3)-1))  Register To Reply

3. ## Re: Two references and two answers

Put this in J5:

=COUNTIFS(\$B:\$B,\$I5,\$C:\$C,"Mike",\$D:\$D,J\$4)

and copy it to K5:L5. The formula in L5 needs a slight amendment to the range:

=COUNTIFS(\$B:\$B,\$I5,\$C:\$C,"Mike",\$E:\$E,L\$4)

The formula can be copied down if you put other dates in column I.

Hope this helps.

Pete  Register To Reply

4. ## Re: Two references and two answers

Hi,

Enter this formula in cell J5 and copy across to the right:

=SUMPRODUCT((\$B\$2:\$B\$37=\$I5)*(\$C\$2:\$C\$37=TRIM(SUBSTITUTE(\$J\$3,"stats","")))*(\$D\$2:\$E\$37=J4))

Regards  Register To Reply

5. ## Re: Two references and two answers

Thanks guys, awesome got it to work with your help!  Register To Reply