MS Excel SUMPRODUCT help

[tyemm]

Returning after a lengthy absence (had a son at age 60, now 2):
I can't believe how skills atrophy!!

Here's my sumproduct, on some voting data, which consists of 37592 rows:
SUMPRODUCT(1*(Data!$AR$2:$DB$37592=$C$1)) where $C$1 is an election date, gives me a count of those who voted on that date
SUMPRODUCT(1*(Data!$AR$2:$DB$37592=$D$1)) where $D$1 is another election date, gives me a count of those who voted then
but when I put them together, looking for those who voted on both dates, as in
SUMPRODUCT((Data!$AR$2:$DB$37592=$C$1)*(Data!$AR$2:$DB$37592=$D$1))
I'm getting zero, when I know and can see by inspection that this is not true.
Something very easy is eluding my gaze; what could it be?
Many thanks,
T

 

[SkipVought]

Hi,

Not having a view of your data, can't make a cogent reply.

Plz post a SMALL (4 columns, 4 rows) of data that demonstrates this issue, AND state the expected result given the test set.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

here's the example (2x2) I made for myself and its obvious why it would not produce what you are expecting...
[pre]
1/2/2017 1/4/2017
1/3/2017 1/5/2017
[/pre]

My formula...
[tt]
=SUMPRODUCT(--($A$2:$B$3=D3)*($A$2:$B$3=D4))
[/tt]
The first & second formula produces these arrays...
[tt]
1: {TRUE, FALSE, FALSE, FALSE}
2: {FALSE, TRUE, FALSE, FALSE}
[/tt]
...so there's no TRUE values in the same slot.

Or am I missing something?





Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Deniall]

I'm not sure exactly what it is you are trying to do.[ ] You say that $D$1 contains a date that is DIFFERENT from the date in $C$1 ("another" date).[ ] How then can any cell in the range $AR$2:$DB$37592 ever be equal to both of those dates?

 

[tyemm]

How is this example, with my data in array B24 and the two voting dates of interest in cells C6 and C7

Name voted1 voted2 voted3
Terry 11/8/16 11/5/13 9/10/13
Than 11/5/13 9/10/13 11/4/08
Tim 11/8/16 11/4/12 11/5/13

Voters who voted on: 11/5/13 is 3 =SUMPRODUCT(($B$2:$D$4=$C$6)*1)
Voters who voted on: 9/10/13 is 2 =SUMPRODUCT(($B$2:$D$4=$C$7)*1)
Voters who voted on both dates: is 0 =SUMPRODUCT(($B$2:$D$4=$C$6)*($B$2:$D$4=$C$7))
(should return 1, not 0)

 

[SkipVought]

[pre]
Name voted1 voted2 voted3 11/5/2013 9/10/2013 BOTH
Terry 11/8/2016 11/5/2013 9/10/2013 1 1 1
Than 11/5/2013 9/10/2013 11/4/2008 1 1 1
Tim 11/8/2016 11/4/2012 11/5/2013 1 0 0
[/pre]
the formula...
[tt]
F2: =SUMPRODUCT(--($B2:$D2=F$1))
H2: =F2*G2
[/tt]
COPY F2 PASTE F2:G4
COPY H2 PASTE H2:H4

Your solution will ALWAYS return ZERO as I explained in my previous post.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[tyemm]

Thanks Skip (& Deniall)--
What I'm aiming for is obviously the sum of the column you've labeled "Both" or 2 in your example above.

 

[SkipVought]

Yes!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Deniall]

Your approach seems to be based on a (new-parenthood-induced?) misunderstanding of how SUMPRODUCT works.[ ] Each of the two parenthesised logical expressions in your formula resolve to a logical array of size 37591 rows by 63 columns.[ ] Each element in each of these arrays will be either TRUE if the corresponding element on the spreadsheet satisfies the criterion, or FALSE if it does not.[ ] You have then asked Excel to multiply these two logical arrays together.[ ] So Excel (in effect) converts every TRUE to 1 and every FALSE to 0, and then creates a third 37591x63 array where each element of the new array is equal to the product of the two corresponding elements of the two arrays being "multiplied".[ ] This is the "product" part of SUMPRODUCT. Finally it does the "sum" part, by adding up all the elements in the third array.

There is no way I can see that would allow you to use SUMPRODUCT to achieve what you are trying to do.[ ] It cannot allow for the fact that the dates you are searching for can be in any of the columns.