complicated conditional sumproduct

[BillyBuckets]

I will try my best to explain this one. First, see my image below:


It's a list of lectures for med school. I am looking for an elegant formula that can do the following:

For each lecture (row), I want to find the product of column C and the number of times a particular date appears in the right columns in that particular row in columns E and beyond.

for example, say I wanted to see how many hours of lecture material I reviewed on 1/16/09. The formula should find that 1/16/09 appears in rows 5, 10, and 11 1 time each. I need it to =sum(1*C5,1*C10,1*C11). I also need this formula to be generalized, i.e., I can edit the date in the formula and it will SUM the PRODUCT of column C and the COUNT of a certain date in only the matching row.

I know how to make a really long, brute force formula (see below, using a separate function for each row), but this leads to HUGE formulas since I am dealing with dozens of rows. Is there an elegant way?


Here's a small sample of the formula I use right now...
COUNTIFS($E$2:$XFD$2,today())*$C$2+COUNTIFS($E$3:$XFD$3,today())*$C$3+COUNTIFS($E$4:$XFD$4,today())*$C$4+COUNTIFS...etc.

 

[Sandeep Warrier]

Assuming cell H1 contains your date reference (you can enter any date in H1 & it'll look for that date in the columns) you could try the following:

=SUMPRODUCT(--($E$2:$E$65536=$H$1),($C2:$C$65536))

This will look onlu in Column E.

If you want to include the other columns, try

=SUMPRODUCT(--($E$2:$E$65536=$H$1),($C2:$C$65536))+SUMPRODUCT(--($F$2:$F$65536=$H$1),($C2:$C$65536))+SUMPRODUCT(--($G$2:$G$65536=$H$1),($C2:$C$65536))

You could extend the formula for N number of columns.

 

[BillyBuckets]

Is there a way that I could expand the number of columns automatically, or do I have to just copy/paste the original:

=sumproduct(--($E$2:$E$31=M2),($C$2:$C$31))
(for example)

over and over in the function bar and manually change all of the "$E$2:$E$31" to "$F$2:$F$31", "$G$2:$G$31", etc.?

Edit: What I mean is that I had to type all of the red stuff manually. can that be done automatically?

=SUM(SUMPRODUCT(--($E$2:$E$31=M2),($C$2:$C$31)),SUMPRODUCT(--($F$2:$F$31=M2),($C$2:$C$31)),SUMPRODUCT(--($G$2:$G$31=M2),($C$2:$C$31)),SUMPRODUCT(--($H$2:$H$31=M2),($C$2:$C$31)),SUMPRODUCT(--($I$2:$I$31=M2),($C$2:$C$31)))

 

[Sandeep Warrier]

I'll havta look into it, but I think that you'll need to manually change the references.