sumproduct with more than one date

[CC]

Hi,

I used the formulae below to generate a total and a count based on two
criteria, a date and a category:

For a count:
=sumproduct(--(b2:b657=date(2006,4,20)),--(f2:f657="d"))
To sum column G when those conditions are met:
=sumproduct(--(b2:b657=date(2006,4,20)),--(f2:f657="d"),g2:g657)


I now need to sum and count from more than one date (e.g. the
cumulative total of houses for sale on 20/04/2006 AND 27/04/2006 and
the total value of them). I have tried simply adding the second date to

the sumproduct formulae above using *, OR, but that didn't work. Any
suggestions?


CC

 

[papou]

Hello
Not tested:
=sumproduct((b2:b657=date(2006,4,20))*(b2:b657=date(2006,4,27))*(f2:f657="d"),g2:g657)

HTH
Cordially
Pascal

 

[Bob Phillips]

=SUMPRODUCT(--((B2:B657=DATE(2006,4,20))+(B2:B657=DATE(2006,4,27))),--(F2:F6
57="d"),G2:G657)


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[Guest]

Hi

Just add another SUMPRODUCT with the other date:
For a count:
=sumproduct(--(b2:b657=date(2006,4,20)),--(f2:f657="d")) +
sumproduct(--(b2:b657=date(2006,4,27)),--(f2:f657="d"))
To sum column G when those conditions are met:
=sumproduct(--(b2:b657=date(2006,4,20)),--(f2:f657="d"),g2:g657) +
=sumproduct(--(b2:b657=date(2006,4,27)),--(f2:f657="d"),g2:g657)

Hope this helps.
Andy.

 

[CC]

Hi,

Papou - didn't work but thanks anyway (I'd tried that already).

Bob - didn't work but looks like it should

....and the prize goes to *drum roll* ... Andy!

Works fine, simple really!

Thanks again for the advice.

CC

 

[Bob Phillips]

Worked for me.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)