Filling Out a Matrix for Excel Using Survey Data

Question

I have a table with two fields. The first is where somebody is, and the second is who showed up. Using this I want to create a table with the amount of occurrences of a somebody showing up over a certain time.

But I can't seem to get it to scan only one row of data at a time using COUNTIF to make sure that both places are satisfied and I can seem to use IF statements correctly. Does anyone have an ideas on what commands would work? I've tried COUNTIFS, COUNTIF, IF and AND statements together but none seem to do this autofilling nature like I want.

Alex M · Answer

If you've attempted COUNTIFS using correct syntax, I'm 90% sure your problem is with locking ranges and references. COUNTIF is exactly the function you want for this task.
Assuming the data is in locations as I've shown, this formula in B2 will copy across and down without restriction:
=COUNTIFS($E$2:$E$10,$A2,$F$2:$F$10,B$1)
Pay close attention to which cell reference arguments are locked (using $) and which are not.

Filling Out a Matrix for Excel Using Survey Data

One Answer

Add your own answers!

Ask a Question