Question> Sumproduct multiple variables... count only certain results

C

collegeitdept

Board Regular
Joined
Nov 14, 2008
Messages
185
Hello I need a Sumproduct like formula that will return the count of how many weeks there are for Season 1 (or variable # for season) for a certain show & platform:





[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Show1[/TD]
[TD]Show1[/TD]
[TD]Show1[/TD]
[TD]Show1[/TD]
[TD]Show2[/TD]
[TD]Show2[/TD]
[TD]Show2[/TD]
[TD]Show2[/TD]
[TD]Show3[/TD]
[TD]Show3[/TD]
[TD]Show3[/TD]
[TD]Show3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Date[/TD]
[TD]Platform1[/TD]
[TD]Platform2[/TD]
[TD]Platform3[/TD]
[TD]Season[/TD]
[TD]Platform1[/TD]
[TD]Platform2[/TD]
[TD]Platform3[/TD]
[TD]Season[/TD]
[TD]Platform1[/TD]
[TD]Platform2[/TD]
[TD]Platform3[/TD]
[TD]Season[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]9/15[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]368[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]139[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]6945[/TD]
[TD][/TD]
[TD]4312[/TD]
[TD]168[/TD]
[TD]765[/TD]
[TD]1[/TD]
[TD]864[/TD]
[TD]25357[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]20369[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]9/22[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]7118[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]19645[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]56355[/TD]
[TD][/TD]
[TD]8763[/TD]
[TD]854[/TD]
[TD]8254[/TD]
[TD]1[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]20369[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]978[/TD]
[TD]852[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]9/29[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]17899[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]648[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]45[/TD]
[TD]1[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]20369[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]4569[/TD]
[TD]853[/TD]
[TD]1[/TD]
[TD]78465[/TD]
[TD]8978[/TD]
[TD]453[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]10/6[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]20361[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]20369[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]9465[/TD]
[TD]1[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]648[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]139[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]65478[/TD]
[TD]1[/TD]
[TD]1476[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]139[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]20369[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]10/13[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]17726[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]272[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]4535[/TD]
[TD]1[/TD]
[TD]8654[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]20369[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1235[/TD]
[TD]1[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]327[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]865[/TD]
[TD]7856[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]10/20[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]19650[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]177[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]4135[/TD]
[TD]1[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]19645[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]4632[/TD]
[TD]642[/TD]
[TD]1[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]19645[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]327[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]2220[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]10/27[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]12026[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]354[/TD]
[TD]5343[/TD]
[TD][/TD]
[TD]536[/TD]
[TD]4535[/TD]
[TD]21656[/TD]
[TD]1[/TD]
[TD]2435[/TD]
[TD]4547[/TD]
[TD]78[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]11/3[/TD]
[TD]45345[/TD]
[TD]455[/TD]
[TD]8936[/TD]
[TD][/TD]
[TD]548[/TD]
[TD]3213[/TD]
[TD]37856[/TD]
[TD]1[/TD]
[TD]853[/TD]
[TD]6951[/TD]
[TD]2356[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]11/10[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]95[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]218[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]2372[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]2220[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]456[/TD]
[TD]35436[/TD]
[TD]1[/TD]
[TD]321[/TD]
[TD]2356[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]544[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]


The formula I have is this (it doesn't work naturally - because it's applying a condition on results?)

=SUMPRODUCT(($B$2:$M$2="Season")*($B$1:$M$1=SHOW)*($A$3:$A$100>=START OF DATE RANGE)*($A$3:$A$100<=END OF DATE RANGE),$B$3:$M$100=1)



Results should be:



Between Dates

Start of Date Range: 9/22/2013
End of Date Range: 11/10/2013

"Show1"
Season: "1"

Count of episodes: 4



"Show2"
Season: "1"

Count of episodes: 8


"Show3"
Season: "2"

Count of episodes: 4
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
Try this:

=SUMPRODUCT(($B$2:$M$2="Season")*($B$1:$M$1=SHOW)*($A$3:$A$100>=DATEVALUE(START OF DATE RANGE))*($A$3:$A$100<=DATEVALUE(END OF DATE RANGE)),--($B$3:$M$100=1))
Click to expand...

1st: You need to change Date to Datevalue
2nd: You need to add "--()" outside second array to make it return 1 or 0

Hope it helps.
 
Upvote 0
Hi dyang, Did that formula return the desired results for you?
Since the OP is trying to match criteria in both directions (match the column, match the rows), I don't think that a single Sumproduct formula will work.

Here's one way to get the desired results using a combination of Index-Match and Countifs...

The Names should be defined as shown first. The formula in P8 should be array entered with Ctrl-Shift-Enter (not just Enter).
Excel Workbook
OP
2StartOfDateRange9/22/2013
3EndOfDateRange11/10/2013
4
5ShowShow3
6Season2
7
8Count of episodes:4
Sheet
#VALUE!
</td></tr></table></td></tr></table>
 
Upvote 0
Hi JS, Actually the SUMPRODUCT works for it. I've tested it in my local file on college's 3 cases.

JS411 said:
Hi dyang, Did that formula return the desired results for you?
Since the OP is trying to match criteria in both directions (match the column, match the rows), I don't think that a single Sumproduct formula will work.

Here's one way to get the desired results using a combination of Index-Match and Countifs...

The Names should be defined as shown first. The formula in P8 should be array entered with Ctrl-Shift-Enter (not just Enter).

OP
StartOfDateRange
EndOfDateRange
Show
Season
Count of episodes:

<colgroup><col style="width:30px; "><col style="width:113px;"><col style="width:83px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]9/22/2013[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]11/10/2013[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]Show3[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]4[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
P8{=COUNTIFS($A$3:$A$100,">="&StartOfDateRange,$A$3:$A$100,"<="&EndOfDateRange,INDEX($B$3:$M$100,0,MATCH(Show& "|Season",$B$1:$M$1 & "|" & $B$2:$M$2,0)),Season)}

<tbody>
</tbody>
Names in Formulas
CellNameApplies to
P8EndOfDateRange=Sheet1!$P$3
P8Season=Sheet1!$P$6
P8Show=Sheet1!$P$5
P8StartOfDateRange=Sheet1!$P$2

<tbody>
</tbody>

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
Click to expand...
 
Upvote 0
Here's the formula (there's 1 column and 1 row shift from college's sample):

Code: 
=SUMPRODUCT(($C$3:$N$3="Season")*($C$2:$N$2="Show1")*($B$4:$B$101>=DATEVALUE("22/9/13"))*($B$4:$B$101<=DATEVALUE("10/11/13")),--($C$4:$N$101=1))

JS411 said:
That's interesting. Could you post the actual formula that you used?
Click to expand...
 
Upvote 0
Hmm....:confused: I've shifted the row and column by 1, but that doesn't work for me. It returns a #VALUE! error.

Stepping through this with the evaluate formula tool, I don't see how that could reliably work.
That formula will ultimately be multiplying a 1 x 12 vector by a 1 x 98 vector, evaluating to #VALUE! for any rows after the first 12.

Can you explain how the formula overcomes that (or post an example file to a hosting site)?
It would be really cool if that works, but I don't see how it could.
 
Upvote 0
Excel 2010
ABCDEFGHIJKLMNOP

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Show1[/TD]
[TD="bgcolor: #FAFAFA"]Show1[/TD]
[TD="bgcolor: #FAFAFA"]Show1[/TD]
[TD="bgcolor: #FAFAFA"]Show1[/TD]
[TD="bgcolor: #FAFAFA"]Show2[/TD]
[TD="bgcolor: #FAFAFA"]Show2[/TD]
[TD="bgcolor: #FAFAFA"]Show2[/TD]
[TD="bgcolor: #FAFAFA"]Show2[/TD]
[TD="bgcolor: #FAFAFA"]Show3[/TD]
[TD="bgcolor: #FAFAFA"]Show3[/TD]
[TD="bgcolor: #FAFAFA"]Show3[/TD]
[TD="bgcolor: #FAFAFA"]Show3[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Result:[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]Date[/TD]
[TD="bgcolor: #FAFAFA"]Platform1[/TD]
[TD="bgcolor: #FAFAFA"]Platform2[/TD]
[TD="bgcolor: #FAFAFA"]Platform3[/TD]
[TD="bgcolor: #FAFAFA"]Season[/TD]
[TD="bgcolor: #FAFAFA"]Platform1[/TD]
[TD="bgcolor: #FAFAFA"]Platform2[/TD]
[TD="bgcolor: #FAFAFA"]Platform3[/TD]
[TD="bgcolor: #FAFAFA"]Season[/TD]
[TD="bgcolor: #FAFAFA"]Platform1[/TD]
[TD="bgcolor: #FAFAFA"]Platform2[/TD]
[TD="bgcolor: #FAFAFA"]Platform3[/TD]
[TD="bgcolor: #FAFAFA"]Season[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]15/09/2013[/TD]
[TD="bgcolor: #FAFAFA, align: right"]368[/TD]
[TD="bgcolor: #FAFAFA, align: right"]139[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6945[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]4312[/TD]
[TD="bgcolor: #FAFAFA, align: right"]168[/TD]
[TD="bgcolor: #FAFAFA, align: right"]765[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]864[/TD]
[TD="bgcolor: #FAFAFA, align: right"]25357[/TD]
[TD="bgcolor: #FAFAFA, align: right"]20369[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]22/09/2013[/TD]
[TD="bgcolor: #FAFAFA, align: right"]7118[/TD]
[TD="bgcolor: #FAFAFA, align: right"]19645[/TD]
[TD="bgcolor: #FAFAFA, align: right"]56355[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]8763[/TD]
[TD="bgcolor: #FAFAFA, align: right"]854[/TD]
[TD="bgcolor: #FAFAFA, align: right"]8254[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]20369[/TD]
[TD="bgcolor: #FAFAFA, align: right"]978[/TD]
[TD="bgcolor: #FAFAFA, align: right"]852[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]29/09/2013[/TD]
[TD="bgcolor: #FAFAFA, align: right"]17899[/TD]
[TD="bgcolor: #FAFAFA, align: right"]648[/TD]
[TD="bgcolor: #FAFAFA, align: right"]45[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]20369[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4569[/TD]
[TD="bgcolor: #FAFAFA, align: right"]853[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]78465[/TD]
[TD="bgcolor: #FAFAFA, align: right"]8978[/TD]
[TD="bgcolor: #FAFAFA, align: right"]453[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]06/10/2013[/TD]
[TD="bgcolor: #FAFAFA, align: right"]20361[/TD]
[TD="bgcolor: #FAFAFA, align: right"]20369[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9465[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]648[/TD]
[TD="bgcolor: #FAFAFA, align: right"]139[/TD]
[TD="bgcolor: #FAFAFA, align: right"]65478[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1476[/TD]
[TD="bgcolor: #FAFAFA, align: right"]139[/TD]
[TD="bgcolor: #FAFAFA, align: right"]20369[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA, align: right"]13/10/2013[/TD]
[TD="bgcolor: #FAFAFA, align: right"]17726[/TD]
[TD="bgcolor: #FAFAFA, align: right"]272[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4535[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]8654[/TD]
[TD="bgcolor: #FAFAFA, align: right"]20369[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1235[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]327[/TD]
[TD="bgcolor: #FAFAFA, align: right"]865[/TD]
[TD="bgcolor: #FAFAFA, align: right"]7856[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA, align: right"]20/10/2013[/TD]
[TD="bgcolor: #FAFAFA, align: right"]19650[/TD]
[TD="bgcolor: #FAFAFA, align: right"]177[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4135[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]19645[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4632[/TD]
[TD="bgcolor: #FAFAFA, align: right"]642[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]19645[/TD]
[TD="bgcolor: #FAFAFA, align: right"]327[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2220[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA, align: right"]27/10/2013[/TD]
[TD="bgcolor: #FAFAFA, align: right"]12026[/TD]
[TD="bgcolor: #FAFAFA, align: right"]354[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5343[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]536[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4535[/TD]
[TD="bgcolor: #FAFAFA, align: right"]21656[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2435[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4547[/TD]
[TD="bgcolor: #FAFAFA, align: right"]78[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FAFAFA, align: right"]03/11/2013[/TD]
[TD="bgcolor: #FAFAFA, align: right"]45345[/TD]
[TD="bgcolor: #FAFAFA, align: right"]455[/TD]
[TD="bgcolor: #FAFAFA, align: right"]8936[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]548[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3213[/TD]
[TD="bgcolor: #FAFAFA, align: right"]37856[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]853[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6951[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2356[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10/11/2013[/TD]
[TD="bgcolor: #FAFAFA, align: right"]95[/TD]
[TD="bgcolor: #FAFAFA, align: right"]218[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2372[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]2220[/TD]
[TD="bgcolor: #FAFAFA, align: right"]456[/TD]
[TD="bgcolor: #FAFAFA, align: right"]35436[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]321[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2356[/TD]
[TD="bgcolor: #FAFAFA, align: right"]544[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]P1[/TH]
[TD="align: left"]=SUMPRODUCT(($B$2:$M$2="Season")*($B$1:$M$1="Show1")*($A$3:$A$92>=DATEVALUE("22/9/13"))*($A$3:$A$92<=DATEVALUE("10/11/13")),--($B$3:$M$92=1))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

JS411 said:
Hmm....:confused: I've shifted the row and column by 1, but that doesn't work for me. It returns a #VALUE! error.

Stepping through this with the evaluate formula tool, I don't see how that could reliably work.
That formula will ultimately be multiplying a 1 x 12 vector by a 1 x 98 vector, evaluating to #VALUE! for any rows after the first 12.

Can you explain how the formula overcomes that (or post an example file to a hosting site)?
It would be really cool if that works, but I don't see how it could.
Click to expand...
 
Upvote 0
Doesn't work for me neither. The following code works for me: =-SUMPRODUCT(-(A3:A93>=StartOfDateRange);-(A3:A93<=EndOfDateRange);-(OFFSET(A3:A93;0;SUMPRODUCT(-(B1:M1="Show1");-(B2:M2="Season");Columns(B1:M1))-1;ROWS(A3:A93);1)=1)) The last 1 in the formula is the season you want to retreive For Show1 and Show2 it's a 1 and for show3 it's a 2, rest speaks for itself I assume.
 
Upvote 0
dgyang said:
Excel 2010
ABCDEFGHIJKLMNOP

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Show1[/TD]
[TD="bgcolor: #FAFAFA"]Show1[/TD]
[TD="bgcolor: #FAFAFA"]Show1[/TD]
[TD="bgcolor: #FAFAFA"]Show1[/TD]
[TD="bgcolor: #FAFAFA"]Show2[/TD]
[TD="bgcolor: #FAFAFA"]Show2[/TD]
[TD="bgcolor: #FAFAFA"]Show2[/TD]
[TD="bgcolor: #FAFAFA"]Show2[/TD]
[TD="bgcolor: #FAFAFA"]Show3[/TD]
[TD="bgcolor: #FAFAFA"]Show3[/TD]
[TD="bgcolor: #FAFAFA"]Show3[/TD]
[TD="bgcolor: #FAFAFA"]Show3[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Result:[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]Date[/TD]
[TD="bgcolor: #FAFAFA"]Platform1[/TD]
[TD="bgcolor: #FAFAFA"]Platform2[/TD]
[TD="bgcolor: #FAFAFA"]Platform3[/TD]
[TD="bgcolor: #FAFAFA"]Season[/TD]
[TD="bgcolor: #FAFAFA"]Platform1[/TD]
[TD="bgcolor: #FAFAFA"]Platform2[/TD]
[TD="bgcolor: #FAFAFA"]Platform3[/TD]
[TD="bgcolor: #FAFAFA"]Season[/TD]
[TD="bgcolor: #FAFAFA"]Platform1[/TD]
[TD="bgcolor: #FAFAFA"]Platform2[/TD]
[TD="bgcolor: #FAFAFA"]Platform3[/TD]
[TD="bgcolor: #FAFAFA"]Season[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]15/09/2013[/TD]
[TD="bgcolor: #FAFAFA, align: right"]368[/TD]
[TD="bgcolor: #FAFAFA, align: right"]139[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6945[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]4312[/TD]
[TD="bgcolor: #FAFAFA, align: right"]168[/TD]
[TD="bgcolor: #FAFAFA, align: right"]765[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]864[/TD]
[TD="bgcolor: #FAFAFA, align: right"]25357[/TD]
[TD="bgcolor: #FAFAFA, align: right"]20369[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]22/09/2013[/TD]
[TD="bgcolor: #FAFAFA, align: right"]7118[/TD]
[TD="bgcolor: #FAFAFA, align: right"]19645[/TD]
[TD="bgcolor: #FAFAFA, align: right"]56355[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]8763[/TD]
[TD="bgcolor: #FAFAFA, align: right"]854[/TD]
[TD="bgcolor: #FAFAFA, align: right"]8254[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]20369[/TD]
[TD="bgcolor: #FAFAFA, align: right"]978[/TD]
[TD="bgcolor: #FAFAFA, align: right"]852[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]29/09/2013[/TD]
[TD="bgcolor: #FAFAFA, align: right"]17899[/TD]
[TD="bgcolor: #FAFAFA, align: right"]648[/TD]
[TD="bgcolor: #FAFAFA, align: right"]45[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]20369[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4569[/TD]
[TD="bgcolor: #FAFAFA, align: right"]853[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]78465[/TD]
[TD="bgcolor: #FAFAFA, align: right"]8978[/TD]
[TD="bgcolor: #FAFAFA, align: right"]453[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]06/10/2013[/TD]
[TD="bgcolor: #FAFAFA, align: right"]20361[/TD]
[TD="bgcolor: #FAFAFA, align: right"]20369[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9465[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]648[/TD]
[TD="bgcolor: #FAFAFA, align: right"]139[/TD]
[TD="bgcolor: #FAFAFA, align: right"]65478[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1476[/TD]
[TD="bgcolor: #FAFAFA, align: right"]139[/TD]
[TD="bgcolor: #FAFAFA, align: right"]20369[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA, align: right"]13/10/2013[/TD]
[TD="bgcolor: #FAFAFA, align: right"]17726[/TD]
[TD="bgcolor: #FAFAFA, align: right"]272[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4535[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]8654[/TD]
[TD="bgcolor: #FAFAFA, align: right"]20369[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1235[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]327[/TD]
[TD="bgcolor: #FAFAFA, align: right"]865[/TD]
[TD="bgcolor: #FAFAFA, align: right"]7856[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA, align: right"]20/10/2013[/TD]
[TD="bgcolor: #FAFAFA, align: right"]19650[/TD]
[TD="bgcolor: #FAFAFA, align: right"]177[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4135[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]19645[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4632[/TD]
[TD="bgcolor: #FAFAFA, align: right"]642[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]19645[/TD]
[TD="bgcolor: #FAFAFA, align: right"]327[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2220[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA, align: right"]27/10/2013[/TD]
[TD="bgcolor: #FAFAFA, align: right"]12026[/TD]
[TD="bgcolor: #FAFAFA, align: right"]354[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5343[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]536[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4535[/TD]
[TD="bgcolor: #FAFAFA, align: right"]21656[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2435[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4547[/TD]
[TD="bgcolor: #FAFAFA, align: right"]78[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FAFAFA, align: right"]03/11/2013[/TD]
[TD="bgcolor: #FAFAFA, align: right"]45345[/TD]
[TD="bgcolor: #FAFAFA, align: right"]455[/TD]
[TD="bgcolor: #FAFAFA, align: right"]8936[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]548[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3213[/TD]
[TD="bgcolor: #FAFAFA, align: right"]37856[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]853[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6951[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2356[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10/11/2013[/TD]
[TD="bgcolor: #FAFAFA, align: right"]95[/TD]
[TD="bgcolor: #FAFAFA, align: right"]218[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2372[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]2220[/TD]
[TD="bgcolor: #FAFAFA, align: right"]456[/TD]
[TD="bgcolor: #FAFAFA, align: right"]35436[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]321[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2356[/TD]
[TD="bgcolor: #FAFAFA, align: right"]544[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]P1[/TH]
[TD="align: left"]=SUMPRODUCT(($B$2:$M$2="Season")*($B$1:$M$1="Show1")*($A$3:$A$92>=DATEVALUE("22/9/13"))*($A$3:$A$92<=DATEVALUE("10/11/13")),--($B$3:$M$92=1))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...


I'm not getting your formula to work either.

Any solutions?
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Count cell in multiple columns based on criteria found in other multiple columns
Replies
2
Views
460
Peter_SSs
Peter_SSs
N
  • Solved
SUMPRODUCT/COUNTIF but only count once
Replies
3
Views
1K
Eric W
Eric W
O
  • Question Question
Counting multiple fractions, separated by commas, inside multiple cells, only if the number is larger than a certain threshold(80%)
Replies
8
Views
644
OBE
O
A
  • Question Question
Determine order number based on multiple variables
Replies
0
Views
521
arembr01
A
Unlucky
  • Question Question
COUNT BASED ON DATES WITH CRITERIA
Replies
2
Views
985
Unlucky
Unlucky

Forum statistics

Threads
1,223,897
Messages
6,175,269
Members
452,628
Latest member
dd2

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top