CODEWITHSUNDEEP
excelcountcellcountif
Mac Convery
Mac Convery

Reputation: 25

How to refer to multiple adjacent cells

I have a work sheet in which there are several cells with a specific entry - let's say "A". These are not all in the same rows/columns. After each cell is a date. I need to count the number of cells containing "A" which also have a specific date in the cell immediately to its right. I've tried combinations of Countifs and Indirect, with no success. How can I achieve this?

Upvotes: 1

Views: 150

Answers (3)

Kresimir L.
Kresimir L.

Reputation: 2441

You can achieve this with a helper row. Add additional row on top of your Worksheet. In cell "A1" enter formula below. 

=COUNTIFS(A2:A2000,"A",B2:B2000,"YourDate")

Drag this formula to the rightmost of where you have data, then simply sum all values returned by formula.

Upvotes: 0

Tom
Tom

Reputation: 9878

This counts the number of times that there is A in column A and 1 in column B

=SUMPRODUCT(($A$1:$A$5="A")*($B$1:$B$5=1))

This outputs in cell D1

enter image description here

Upvotes: 2

ImaginaryHuman072889
ImaginaryHuman072889

Reputation: 5195

Not too difficult.

I have created a sample sheet with 8 rows and 5 columns of data.

See below, the formula in cell C12 counts the number of occurrences where the a cell with a date of October 31, 2017 is directly to the right of a cell that contains the text A.

enter image description here

If you want more info as to how this works, read on:

When searching for cells that contain A, you don't search in the last column of the data (in this case, column E) because it is impossible for a column to the right to have any date in it. This is why a portion of the formula says A1:D8="A" instead of A1:E8="A". This is the same reasoning why we start searching for a date in column B rather than column A in the formula.

Upvotes: 0

Related Questions