Conditional sum

  • Thread starter Thread starter mark
  • Start date Start date
M

mark

I need help. I have a spreadsheet that has several colums
and 600 rows. It looks something like this:

Individual Modality Impression

DOE 5 TRUE
DOE 5 TRUE
DOOM 4 False
DOE 5 False
CONANA 6 FALSE
DOOM 5 FALSE
DOOM 6 TRUE
CONANA 4 TRUE
DOE 4 FALSE
CONANA 6 TRUE
DOOM 5 TRUE

There is more to differentiate the data, but that is
unimportant to what I want to do. What I want to do is
report compliance for each individual, by modality.
Example: Modality 5: Dr. Doe 67% Dr. Doom 50%.

Any help will be greatly appreciated.
 
Hi Mark,

Another way,

Below your table, you can set-up another one, say starting at A16, with
modality across the top row and names down the left column, something
like this:

4 5 6
Doe
Doom
Conana


B16=4
C16=5
D16=6

A17=Doe
A18=Doom
A19=Conana

Then, put this formula in cell B17, copy across and down

=SUMPRODUCT(($A$2:$A$12=$A17)*($B$2:$B$12=B$16)*($C$2:$C$12=TRUE))/SUMPRO
DUCT(($A$2:$A$12=$A17)*($B$2:$B$12=B$16))

Hope this helps!
 
Thanks for the idea.
-----Original Message-----
Hi Mark,

Another way,

Below your table, you can set-up another one, say starting at A16, with
modality across the top row and names down the left column, something
like this:

4 5 6
Doe
Doom
Conana


B16=4
C16=5
D16=6

A17=Doe
A18=Doom
A19=Conana

Then, put this formula in cell B17, copy across and down

=SUMPRODUCT(($A$2:$A$12=$A17)*($B$2:$B$12=B$16)* ($C$2:$C$12=TRUE))/SUMPRO
DUCT(($A$2:$A$12=$A17)*($B$2:$B$12=B$16))

Hope this helps!


.
Click to expand...
 
Back
Top