SumIf/SumProduct Formula Help
 

I have a worksheet containing the following:
Name ID Mon Tue Wed Thur Fri Sat Sun Avg

For each ID, there may be multiple listings of the same name, because each
day has to have its own row. What I want to do is create a formula that will
only read the "Mon" information for each name OR the "avg" line, if there is
no Mon. Our current function is a simple =sumif using the name and the
monday line, but it requires us to do some additional work to add on the
"avg" line after the printout is complete.

I think the sumproduct function might be useful here, but I'm not sure how
to apply it in this situation ... any ideas?

If ID is in B, Mon In C, Avg in J, then try

=IF(B2:B10="a",SUM(IF(C2:C10<"",C2:C10,J2:J10)))

which is an array formula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jacinthe" wrote in message
...
I have a worksheet containing the following:
Name ID Mon Tue Wed Thur Fri Sat Sun Avg

For each ID, there may be multiple listings of the same name, because each
day has to have its own row. What I want to do is create a formula that
will
only read the "Mon" information for each name OR the "avg" line, if there
is
no Mon. Our current function is a simple =sumif using the name and the
monday line, but it requires us to do some additional work to add on the
"avg" line after the printout is complete.

I think the sumproduct function might be useful here, but I'm not sure how
to apply it in this situation ... any ideas?

I found a formula that works even smoother - I'm not sure why, but here it is:

=IF(SUMIF($B$8:$B$4140,A5,$L$8:$L$4140)=0,SUMIF($B $8:$B$4140,A5,$Z$8:$Z$4140),SUMIF($B$8:$B$4140,A5, $L$8:$L$4140))

In this formula, A is the reference name, B is the name in the worksheet, L
is the Monday column, and Z is the Average column. Thanks so much for your
help!

"Bob Phillips" wrote:

If ID is in B, Mon In C, Avg in J, then try

=IF(B2:B10="a",SUM(IF(C2:C10<"",C2:C10,J2:J10)))

which is an array formula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)