SUMPRODUCT with PLACEHOLDERS?

[titushanke]

Hi, I am stuck here with something or maybe it's just because it'
Monday morning..

I have a source sheet with values in rows and columns and an outpu
sheet which should sum the values in column F6-F999 if the values mee
the following criteria:

- date in E6-E999 = date in output$G$3
- value in C6-C99 = BR9

This is my formula and it works fine.

=SUMPRODUCT(VISION!$F$6:$F$999*(VISION!$E$6:$E$999=OUTPUT!G$3)*(VISION!$C$6:$C$999=\"BR9\")

Now I discovered though that sometimes the values in C6-C99 var
slighlty, such as:

BR9
BR9A
BR9B
BR9C
etc.

I want SUMPRODUCT to include all variations of BR9.
In a different case when I had to use the VLOOKUP formula, I used th
following syntax:

\"BR9\"&\"*\

so the formula looks like this:

=SUMPRODUCT(VISION!$F$6:$F$999*(VISION!$E$6:$E$999=OUTPUT!G$3)*(VISION!$C$6:$C$999=\"BR9\"&\"*\")

Unfortunately this returns 0 results so I believe SUMPRODUCT doesn'
understand the syntax...

Anybody got a clue?
Thanks for your help,

Titus

 

[titushanke]

....one more thing.

sometimes the value I want to look up is not BR9, but B9A, for
example.

Is there a more elegant way than two look these two possibilities up by
adding another sumproduct formula such as:

sumproduct(..."BR9...")+sumproduct(..."B9...")?

Thanks for your help!!
Titus

 

[Dav]

=SUMPRODUCT(VISION!$F$6:$F$999*(VISION!$E$6:$E$999
=OUTPUT!G$3)*(VISION!$C$6:$C$999="BR9"&"*"))

if all your differences are after the first 3 characters eg
left(cell,3) is the same in each instance you could try

=SUMPRODUCT((VISION!$F$6:$F$999)*(VISION!$E$6:$E$999
=OUTPUT!G$3)*(left(VISION!$C$6:$C$999,3)="BR9"))

regards

Dav

 

[Bob Phillips]

if you want all starting with BR9

=SUMPRODUCT(--(VISION!$F$6:$F$999),--(VISION!$E$6:$E$999=OUTPUT!G$3),--(LEFT
(VISION!$C$6:$C$999)="BR9"))

all containing BR9

=SUMPRODUCT(--(VISION!$F$6:$F$999),--(VISION!$E$6:$E$999=OUTPUT!G$3),--(ISNU
MBER(FIND("BR9",VISION!$C$6:$C$999))))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[titushanke]

Hi there! Thanks for the quick help!
I wish people in my office would respond to my emails the way you guys
do here in the forum! lol

The left() command was exactly what I needed!

Thanks a million, once again!

Greetings from Italy,

Titus.

 

[Bob Phillips]

I am sure they would if you paid them what you pay us <vbg>

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)