Having Excel return the name/locations of cells its counting, possible?

[Jennifre]

I have been using help from folks here, thank you to all!

I am hoping there is a way for Excel to indicate which cells it's getting the data it's counting for me using a formula I have.

To do this: I have totals from multiple columns tallying various criteria. Within these totals, I'd like to look at yet additional criteria.

I know how to count the additional criteria, but I don't know how to get the set I want to look at, or the cells Excel has grabbed its counts from -- is that possible?

The SUMPRODUCT formula I'm using (basically) is below. Is there a way to have Excel return which cells it grabs this data from in its SUMPRODUCT counting process? Or in a formula I can put into an adjacent cell to get the cells it has counted?

=SUMPRODUCT(--((B2:B1364=2)+(B2:B1364=3)),--((D2:D1364=8)+(D2:D1364=7)+(D2:D1364=6)+(D2:D1364=5)+(D2:D1364=4)),--((BK2:BK1364=4)))

 

[shg]

It's counting the rows in which all of those conditions exist simultaneously, so it is counting the rows.

Another formula for your collection:

=SUMPRODUCT((BO2:BO1364={1,2}) * (BU2:BU1364=6) * (BT2:BT1364=1))

 

[Jennifre]

Hi,

I totaled the responses by these criteria (total A), and then per possible responses (total B, total C, and total D -- there are 3 possible responses) in another column. And got 23 responses (totaling B, C, and D) more than the initial total A.

Hence I figured each criteria I was counting was not exclusive to that set, and included some overlap of the criteria.

So, two questions:

- What in this formula indicates it is counting matches per row?
- Can anyone here see how it could be totaling more responses than in total !.

 

[shg]

Can you post a simple example of the data (a dozen lines or so) that doesn't work?

 

[Aladin Akyurek]

HI thanks for your edits.

You are welcome.

I need to emphasize that I need a count of the ROWS whose cells I have noted in the formula meet each of these criteria.

So not a simple count of the cells containing that criteria, but the ROWS whose cells meet each of these criteria.

Is there a way to include that I would like a count of these criteria, but only per any ROW that satisfies that criteria?

(Background -- the columns contain responses per survey question. The rows are the answers each survey respondent submitted.)

Thanks. I really appreciate your help!!!!

Let's have a look at:

BO2: 1
BO2: 2
BO3: 3
BO4: 1

BU2: 6
BU3: 6
BU4: 7
BU5: 6

BT2: 1
BT3: 1
BT4: 1
BT5: 1

The formula as edited and adjusted for the above range:

=SUMPRODUCT((BO2:BO5=1)+(BO2:BO5=2),--(BU2:BU5=6),--(BT2:BT5=1))

should yield 3 and it arrives at this count as follows, shown per row:

2:: TRUE+FALSE, --TRUE, --TRUE
3:: FALSE+TRUE, --TRUE, --TRUE
4:: FALSE+FALSE, --FALSE, --TRUE
5:: TRUE+FALSE, --TRUE, --TRUE

Note that a + between two logical values or -- in front of a logical value
transform a TRUE into 1 and a FALSE into a 0. By this, we now have:

2:: 1,1,1
3:: 1,1,1
4:: 0,0,1
5:: 1,1,1

Since comma means multiply in SumProduct, we get:

2:: 1
3:: 1
4:: 0
5:: 1

The SumProduct's Sum routine ultimately sums these 1's and 0's and arrives at 3 as result. We can in fact say that: "it is counting matches per row."

 

[Jennifre]

Thank you so much. I think I've had a bunch of things replied that hopefully will actually have me learning a bit about this.

Of course, the issue was only data that was changed as I dragged formulas across cells, and I'd not noticed the change happening.

I've been trying to understand if there is any rhyme or reason behind under which circumstances Excel updates the data as you drag. Sometimes it makes sense, and sometimes I'm just confused about why and how it happens with formulas.

Any advice toward that end would be quite appreciated.

As I looked closely at my data, that's why things were tallying up differently, and I'd not expected any formula change so hadn't looked carefully at where I needed to be looking.

 

[Aladin Akyurek]

Thank you so much. I think I've had a bunch of things replied that hopefully will actually have me learning a bit about this.

Of course, the issue was only data that was changed as I dragged formulas across cells, and I'd not noticed the change happening.

I've been trying to understand if there is any rhyme or reason behind under which circumstances Excel updates the data as you drag. Sometimes it makes sense, and sometimes I'm just confused about why and how it happens with formulas.

Any advice toward that end would be quite appreciated.

As I looked closely at my data, that's why things were tallying up differently, and I'd not expected any formula change so hadn't looked carefully at where I needed to be looking.

You need indeed lock the ranges when you copy down such formulas. I'd suggest adopting the method I suggested in post #3 though.