TransWikia.com

Filling Out a Matrix for Excel Using Survey Data

Super User Asked by SpiderCrawler on December 18, 2021

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.

This is an example

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.

One 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.

enter image description here

Answered by Alex M on December 18, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP