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

[Wad Mabbit]

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

 

[jasonb75]

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.

 

[Wad Mabbit]

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)

 

[jasonb75]

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

 

[Wad Mabbit]

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

 

[Wad Mabbit]

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?

 

[Wad Mabbit]

Like that would work , sigh...
=IF(@[D4 Activity]=Satisfactory,1,0) in the helper column

 

[jasonb75]

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

Volatile Excel Functions -Decision Models

Microsoft Excel Calculation Secrets and optimisation tips, calculation methods, calculation sequence, dependencies and memory limits.

www.decisionmodels.com

Like that would work , sigh...
=IF(@[D4 Activity]=Satisfactory,1,0) in the helper column

Why the sad face, it should work??

 

[Wad Mabbit]

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?

 

[jasonb75]

like I'd need 30 or 40 extra columns, in a 300 row table.

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.