One column of data with multiple criteria dependent upon row position.

[rtraffic]

I need to count the number of instances when the term "Three cameras" or "Four cameras" shows up and when the term "Standard camera" shows up 3 rows prior and multiply the respective array "3 or 4" to get a final standard camera count. The data structure can not change

An example would be: Final answer = 7 standard cameras

Cell A01 - not relevant data
Cell A02 - Standard camera
Cell A03 - not relevant data
Cell A04 - not relevant data
Cell A05 - Three cameras
Cell A06 - not relevant data
Cell A07 - Non Standard
Cell A09 - not relevant data
Cell A10 - not relevant data
Cell A11 - Three cameras
Cell A12 - not relevant data
Cell A13 - Standard camera
Cell A14 - not relevant data
Cell A15 - not relevant data
Cell A16 - Four cameras

 

[Jerry Sullivan]

Hi and Welcome to the Board,

This Array Formula** should work.

=SUM(IF(IF(A2:A13="Standard camera",A5:A16,0)="Three cameras",3,0)+IF(IF(A2:A13="Standard camera",A5:A16,0)="Four cameras",4,0))

**Array formulas need to be entered using a combination of Ctrl, Shift, Enter (not just Enter).
So, after pasting that formula with your cursor still in that Cell, hold down both the Ctrl key and the Shift key, then hit Enter.


EDIT: Your example was missing a reference to A8, so I pasted the data into A2:A16. You can adjust the range as needed.