SUMPRODUCT & SUBTOTAL with same criteria ("S") across two non-contiguous columns [D3 Activity] & [D4 Activity] to return count of [Name] in either

Wad Mabbit

Board Regular
Joined
Mar 31, 2016
Messages
74
Office Version
  1. 2016
Platform
  1. Windows
Hi, I have:
SUMPRODUCT((tblStudentProgress[D3 Activity]=Satisfactory)*(SUBTOTAL(103,OFFSET(AW3,ROW(tblStudentProgress[D3 Activity])-MIN(ROW(tblStudentProgress[D3 Activity])),0)))) +
SUMPRODUCT((tblStudentProgress[D4 Activity]=Satisfactory)*(SUBTOTAL(103,OFFSET(AW3,ROW(tblStudentProgress[D4 Activity])-MIN(ROW(tblStudentProgress[D4 Activity])),0))))

This gives me the count of both, but what I want is that where there is a 'hit' in both columns of a row, it only counts as 1.

So here's what it's all about. I have a table of students. They do a number of activities towards a unit of study. Each activity is noted in a column. In this case, there are activities on days 3 and 4. I'd like to get the count of students that have done either an activity on day 3 or on day 4. I.e. those students that have done at least one activity for that unit. The formula I have works great, I just can't figure how to get it to canvass multiple columns.

And oh yeah, if you could also throw in a formula to count just those students that have a "S" in both columns. E.g. they have done all activities for a unit. And um, because you are real smart, could you also do a variant to ignore those that have "Co" in column tblStudentProgress[D4MSMWHS200]

Unm, I have a third request, to determine if they have participated (done anything), e.g. started studying as opposed to doing stuff all.
I have a formula that works elsewhere, but it's just checks if one student (row). Unsure how to turn this into a SUMPRODUCT/SUBTOTAL:
IF(SUMPRODUCT(--(chk_Participation=tblStudentProgress[@[D4 Resourced]:[D4 Webinar]]))>0,"P",""))

Attached images show the data and the hideously complicated concatenation to produce a 'report'

chk_Participation is a range on another sheet that I use for progress validation:
1
2
3
A
C
Co
F
F2
F3
P
S
U
 

Attachments

  • 2020-08-28_15-59-07.jpg
    2020-08-28_15-59-07.jpg
    122.5 KB · Views: 12
  • What I'm trying to do.jpg
    What I'm trying to do.jpg
    53.5 KB · Views: 13

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
One thing at a time, try this for the first part.

=SUMPRODUCT((((tblStudentProgress[D3 Activity]=Satisfactory)+(tblStudentProgress[D4 Activity]=Satisfactory))>0)*(SUBTOTAL(103,OFFSET(AW3,ROW(tblStudentProgress[D3 Activity])-MIN(ROW(tblStudentProgress[D3 Activity])),0))))

Please see link below for information on posting examples to the forum. There is usually too little information in screen captures for them to be useful.
 
Upvote 0
YAY! Fantastic work! Um, the columns aren't contiguous though. Is there a fix for columns that are non-adjacent? (AN, AS)

SUMPRODUCT((((tblStudentProgress[D3 Activity]=Satisfactory)+(tblStudentProgress[D4 Activity]=Satisfactory))>0)*(SUBTOTAL(103,OFFSET(AW3,ROW(tblStudentProgress[D3 Activity])-MIN(ROW(tblStudentProgress[D3 Activity])),0))))


I set the Offset (AW3) to the first data row in the leftmost column, right? I.e. tblStudentProgress [D3 Activity] column, row 3 (my header row is row 2)
 
Upvote 0
The columns don't need to be adjacent for it to work. They are evaluated individually then the non zero results are counted by row.

Offset is not looking at columns, only which rows are visible / hidden, it could be any column in the table (or sheet) as long as it refers to the same rows as the rest of the formula.
Also, you could make it more efficient by offsetting from the table header instead of the first data row, one less array to process.

=SUMPRODUCT((((tblStudentProgress[D3 Activity]=Satisfactory)+(tblStudentProgress[D4 Activity]=Satisfactory))>0)*(SUBTOTAL(103,OFFSET(tblStudentProgress[[#Headers],[D3 Activity]],ROW(tblStudentProgress[D3 Activity]),0))))

For what it's worth, a better practice method would be to add a Results column to the tblStudentProgress table with the formula

=--OR([@D3 Activity]=Satisfactory,[@D4 Activity]=Satisfactory)

Then get your final result with

=SUBTOTAL(109,tblStudentProgress[Results])
 
Upvote 0
I was trying to be neat and avoid helper columns, but really, I could just bunch them in a group and collapse it.

I'll check how I applied it, but I did have a problem there.. um...
 
Upvote 0
OK, I've gone with that, thanks...

It begs a question...
for the instances where I've just a single column to check, like:
SUMPRODUCT((tblStudentProgress[D9 Activity]=Satisfactory)*(SUBTOTAL(103,OFFSET(BZ3,ROW(tblStudentProgress[D9 Activity])-MIN(ROW(tblStudentProgress[D9 Activity])),0))))

is it worth setting up a helper column --OR(tblStudentProgress[D9 Activity]=Satisfactory)
and grab it using SUBTOTAL(109,tblStudentProgress[D9 Activity])

..would that lead to better performance?
 
Upvote 0
Like that would work :(, sigh...
=IF(@[D4 Activity]=Satisfactory,1,0) in the helper column
 
Upvote 0
The helper column method would perform slightly better than the sumproduct array (less calculation steps) but how much of a performance difference it makes would depend on several other factors, with the number of rows of data in the table and the number of times that the sumproduct formula is used being the 2 main factors.

With the helper columns, you would almost save the effort of everything after the * in the sumproduct formula.

Also by doing away with the OFFSET function you removing volatile calculation from the process (if you don't know what that is, see the link below).

Like that would work :(, sigh...
=IF(@[D4 Activity]=Satisfactory,1,0) in the helper column
Why the sad face, it should work??
 
Upvote 0
Aw, the face was the earlier comment I fid, bad formula.

Um
Can do helper columns but sheesh, like I'd need 30 or 40 extra columns, in a 300 row table.

Just to populate a dashboard.

WHY do I do these things?
 
Upvote 0
like I'd need 30 or 40 extra columns, in a 300 row table. :eek:
In that case, maybe stick to the sumproduct method. It would be less efficient but with amount of data that you have the difference shouldn't be problematic.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,316
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top