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

rtraffic

New Member
Joined
Mar 8, 2012
Messages
1
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
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,164
Messages
5,467,010
Members
406,518
Latest member
SilverSixx

This Week's Hot Topics

Top