dynamic range? help!

  • Thread starter Thread starter baseballkyle
  • Start date Start date
B

baseballkyle

tab1

salesperson 1/31/2007 2/28/2007 3/31/2007
XYZ ? ? ?


tab2

salesperson date cont value
XYZ 1/15/2007 500
XYZ 1/31/2007 1000
XYZ 1/10/2007 850


trying to setup a sumproduct to return XYZ total contract value for
January (that obviously spans over multiple dates). can you assist me
w/ a formula solution to this problem? thanks!!

Kyle
 
ok i'm trying to use this formula: =SUMPRODUCT(--(MONTH(Revenue!$V$9:$V
$3800)=MONTH($N$9)),
--(YEAR(Revenue!$V$9:$V$3800)=YEAR($N$9)),--(Revenue!$D$9:$D
$3800=B26),--(Revenue!$AD$9:$AD$3800))

BUT i'm getting a #VALUE error msg. any ideas? thx!!
 
try this
=SUMPRODUCT(--(MONTH(Revenue!$V$9:$V$3800)=MONTH($N$9)),--(YEAR(Revenue!$V$9:$V$3800)=YEAR($N$9)),--(Revenue!$D$9:$D$3800=B26),Revenue!$AD$9:$AD$3800) or =SUMPRODUCT((MONTH(Revenue!$V$9:$V$3800)=MONTH($N$9))*(YEAR(Revenue!$V$9:$V$3800)=YEAR($N$9))*(Revenue!$D$9:$D$3800=B26)*Revenue!$AD$9:$AD$3800)--Don GuillettMicrosoft MVP ExcelSalesAid (e-mail address removed)"baseballkyle" <[email protected]> wrote in messageok i'm trying to use this formula: =SUMPRODUCT(--(MONTH(Revenue!$V$9:$V> $3800)=MONTH($N$9)),> --(YEAR(Revenue!$V$9:$V$3800)=YEAR($N$9)),--(Revenue!$D$9:$D> $3800=B26),--(Revenue!$AD$9:$AD$3800))>> BUT i'm getting a #VALUE error msg. any ideas? thx!!>>> On Mar 19, 3:18 pm, "Don Guillett" <[email protected]> wrote:>> add another parameter.>>>> -->> Don Guillett>> Microsoft MVP Excel>> SalesAid Software>> (e-mail address removed)"baseballkyle" <[email protected]> wrote inmessage>>>> > what if i need not only specific month, but also specific year? thx!!>>>> > On Mar 19, 2:40 pm, "Don Guillett" <[email protected]> wrote:>> >> try this idea>> >> =sumproduct((month(b2:b22)=1)*(a2:a22="xyz")*c2:c22)>>>> >> -->> >> Don Guillett>> >> Microsoft MVP Excel>> >> SalesAid Software>> >> (e-mail address removed)"baseballkyle" <[email protected]> wrotein>> >> message>>>>>>>> > tab1>>>> >> > salesperson 1/31/2007 2/28/2007 3/31/2007>> >> > XYZ ? ? ?>>>> >> > tab2>>>> >> > salesperson date cont value>> >> > XYZ 1/15/2007 500>> >> > XYZ 1/31/2007 1000>> >> > XYZ 1/10/2007 850>>>> >> > trying to setup a sumproduct to return XYZ total contract value for>> >> > January (that obviously spans over multiple dates). can you assistme>> >> > w/ a formula solution to this problem? thanks!!>>>> >> > Kyle>>>
 
That suggests that some of the data is bad, maybe text in one of the date
fields or a cell with a formula erroring.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
I didn't see where you kept the xyz stuff:

=sumproduct(--(revenue!$a$9:$a$3800="xyz"),
--(text(revenue!$v$9:$v$3800,"yyyymm")=text($n$9,"yyyymm")),
revenue!$ad$9:$ad$3800)

The --(revenue!$ad$9:$ad$3800) portion will cause an error if there are
non-numeric entries in that range.

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html
 
Back
Top