Trying to create a conditional sum

K

KevinK9

I am trying to create a conditional sum, but cannot figure out how to d
it for my case. I will post a few rows from my sheet first.
01/09/04 01/11/04 1 1 50.00% 0
02/19/04 02/21/04 1 1 1 75.00% 1
11/19/04 11/21/04 1 1 1 1 1 125.00% 1
Meeting Attendance % 100.00% 150.00% 100.00% 50.00% 100.00% 0.00%
of Meetings 2

The first two columns are the dates. The next six columns ar
different people. A one in their column would indicate they attende
that meeting. Next column is a total attendance percentage, with
people counting as 100%. The final column is a 1 if cell to the lef
is >74%, otherwise 0.

My conditional sum is:
If attendance is 1, and last column is 0, do not add to sum.
If attendance is 1, and last column is 1, add to sum.
(Then I have to divide that sum by the the # of meetings value for m
percentage)
I am hoping to correct my Meeting Attendance formula.
Corrected, the percentages should be:
Meeting Attendance % 100.00% 100.00% 100.00% 50.00% 50.00% 0.00% # o
Meetings 2
 
A

acw

Kevi

Assuming that your attendance indicator is in the range C1 to H3, and the conditional data of 0 or 1 is in J1:J3 then enter the following formula in C4 and copy across to H
=SUMPRODUCT(C1:C3,$J$1:$J$3)/

I've fixed the denominator as 2 but this can be a reference to a cell

Ton

----- KevinK9 > wrote: ----

I am trying to create a conditional sum, but cannot figure out how to d
it for my case. I will post a few rows from my sheet first
01/09/04 01/11/04 1 1 50.00%
02/19/04 02/21/04 1 1 1 75.00%
11/19/04 11/21/04 1 1 1 1 1 125.00%
Meeting Attendance % 100.00% 150.00% 100.00% 50.00% 100.00% 0.00%
of Meetings

The first two columns are the dates. The next six columns ar
different people. A one in their column would indicate they attende
that meeting. Next column is a total attendance percentage, with
people counting as 100%. The final column is a 1 if cell to the lef
is >74%, otherwise 0.

My conditional sum is
If attendance is 1, and last column is 0, do not add to sum
If attendance is 1, and last column is 1, add to sum
(Then I have to divide that sum by the the # of meetings value for m
percentage
I am hoping to correct my Meeting Attendance formula
Corrected, the percentages should be
Meeting Attendance % 100.00% 100.00% 100.00% 50.00% 50.00% 0.00% # o
Meetings
 
K

KevinK9

Thank you so much for helping me out! It works perfectly, with th
results I expected.

I'm still trying to understand the SUMPRODUCT function...I don'
remember this one from school so many years ago...

Thanks again!

Kevi
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top