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)))

 

[AlphaFrog]

You could use Conditional Formatting to highlight the cells or rows that match the same criteria.

Or you could use Autofilter to select the rows with that criteria.

 

[Aladin Akyurek]

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)))

That formula is too long and therefore unmanageable.

The following would be better:

=SUMPRODUCT(--ISNUMBER(MATCH($B$2:$B$7,{2,3},0)),--ISNUMBER(MATCH($D$2:$D$7,{4,5,6,7,8},0)),--(($BK$2:$BK$7=4)))

If you create ranges for criterion values... say, on a sheet named Criteria...

On Criteria:

A2: 2
A3: 3

Select A2:A3, go the NameBox on the Formula Bar, type BList, and hit enter.

=SUMPRODUCT(--ISNUMBER(MATCH($B$2:$B$7,BList,0)),--ISNUMBER(MATCH($D$2:$D$7,DList,0)),--(($BK$2:$BK$7=4)))

Note. We can calculate the rows this formula counts in as a hit if so desired. Such a list of rows could be very long and not very useful.

B2: 4
B3: 5
B4: 6
B5: 7
B6: 8

Name the range of B2:B6 DList.

Now we can invoke a much nicer formula:

 

[Jennifre]

Thank you!

Aladin, if your first comment is letting me know I could rewrite the formula I am using to be shorter, point taken, though I like that one basically because I am a dork, have used it throughout my data already and am too lazy to change it. I can also understand it so I can edit it easily, which is a plus for me. But thank you for your other option, I appreciate that.

The next point, or how to gather which cells my formula is counting from, if that is unwieldy, which I can see definitely, is there any way to add that to my formula, or to edit it to do something like:

=SUMPRODUCT(--(BD2:BD1364=1),(of these tell me the number of 1 responses, 2 responses, 3 responses))

I have no idea if I am being clear, but I am hoping so. ... Is that possible, so I'll get one response for the first query "=SUMPRODUCT(--(BD2:BD1364=1))"
and 3 responses for the numbers of 1s within that set, 2s within that set, 3s within that set?

I am super novice at Excel, but I have a vague hunch this might be possible... ?

Thank you, so much, again!

 

[T. Valko]

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)))

You could use this in another column to "mark" the rows that are being counted.

Let's assme you can use column A as that column. Enter this formula in A2 and copy down to A1364:

=AND(OR(B2={2,3}),OR(D2={4,5,6,7,8}),BK2=4)

Rows that are being conted will return TRUE.

 

[Aladin Akyurek]

Thank you!

Aladin, if your first comment is letting me know I could rewrite the formula I am using to be shorter, point taken, though I like that one basically because I am a dork, have used it throughout my data already and am too lazy to change it. I can also understand it so I can edit it easily, which is a plus for me. But thank you for your other option, I appreciate that.

The next point, or how to gather which cells my formula is counting from, if that is unwieldy, which I can see definitely, is there any way to add that to my formula, or to edit it to do something like:

=SUMPRODUCT(--(BD2:BD1364=1),(of these tell me the number of 1 responses, 2 responses, 3 responses))

I have no idea if I am being clear, but I am hoping so. ... Is that possible, so I'll get one response for the first query "=SUMPRODUCT(--(BD2:BD1364=1))"
and 3 responses for the numbers of 1s within that set, 2s within that set, 3s within that set?

I am super novice at Excel, but I have a vague hunch this might be possible... ?

Thank you, so much, again!

Are you trying to expand the formula you have with an additonal term, that is, with --(BD2:BD1364=1) ?

 

[Jennifre]

Hi Biff, Aladin, thank you.

=SUMPRODUCT(--((BO2:BO1364=1)+(BO2:BO1364=2)),--((BU2:BU1364=6),--((BT2:BT1364=1)))

About my formula, I want to ensure that what I'm using is doing what I need. Is the quoted formula above giving me the totals of this?

The number of rows that satisfy ALL of these criteria, and if not, how can I modify it to do this?

1s or 2s in BO cells
and
6s in BU cells
and
1s in BT cells

*Biff, I apologize, I'd not thought to specify rows in my initial request for this formula!

 

[Aladin Akyurek]

Hi Biff, Aladin, thank you.



About my formula, I want to ensure that what I'm using is doing what I need. Is the quoted formula above giving me the totals of this?

The number of rows that satisfy ALL of these criteria, and if not, how can I modify it to do this?

1s or 2s in BO cells
and
6s in BU cells
and
1s in BT cells

*Biff, I apologize, I'd not thought to specify rows in my initial request for this formula!

Needs some editing...

=SUMPRODUCT((BO2:BO1364=1)+(BO2:BO1364=2),--(BU2:BU1364=6),--(BT2:BT1364=1))

 

[T. Valko]

Hi Biff, Aladin, thank you.

=SUMPRODUCT(--((BO2:BO1364=1)+(BO2:BO1364=2)),--((BU2:BU1364=6),--((BT2:BT1364=1)))

About my formula, I want to ensure that what I'm using is doing what I need. Is the quoted formula above giving me the totals of this?

The number of rows that satisfy ALL of these criteria, and if not, how can I modify it to do this?

1s or 2s in BO cells
and
6s in BU cells
and
1s in BT cells

*Biff, I apologize, I'd not thought to specify rows in my initial request for this formula!

You have some extra parentheses that we can get rid of. Also:

--((BO2:BO1364=1)+(BO2:BO1364=2))

Since (BO2:BO1364=1)+(BO2:BO1364=2) can only return either 1 or 0 we don't need to use the double unary.

=SUMPRODUCT((BO2:BO1364=1)+(BO2:BO1364=2),--(BU2:BU1364=6),--(BT2:BT1364=1))

I'm kind of partial towards using ISNUMBER(MATCH(...)) when testing one range for multiple conditions.

Also, I like to use cells to hold the criteria:

• A1 = 1
• B1 = 2
• C1 = 6

=SUMPRODUCT(--(ISNUMBER(MATCH(BO2:BO1364,A1:B1,0))),--(BU2:BU1364=C1),--(BT2:BT1364=A1))

 

[Jennifre]

HI thanks for your edits.

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!!!!