SUMPRODUCT with PLACEHOLDERS?
 

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

I have a source sheet with values in rows and columns and an output
sheet which should sum the values in column F6-F999 if the values meet
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 vary
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 the
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't
understand the syntax...

Anybody got a clue?
Thanks for your help,

Titus.


--
titushanke
------------------------------------------------------------------------
titushanke's Profile: http://www.excelforum.com/member.php...o&userid=19997
View this thread: http://www.excelforum.com/showthread...hreadid=519286

SUMPRODUCT with PLACEHOLDERS?
 

...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


--
titushanke
------------------------------------------------------------------------
titushanke's Profile: http://www.excelforum.com/member.php...o&userid=19997
View this thread: http://www.excelforum.com/showthread...hreadid=519286

SUMPRODUCT with PLACEHOLDERS?
 

=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$9 99
=OUTPUT!G$3)*(left(VISION!$C$6:$C$999,3)="BR9"))

regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=519286

SUMPRODUCT with PLACEHOLDERS?
 

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" wrote
in message ...

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

I have a source sheet with values in rows and columns and an output
sheet which should sum the values in column F6-F999 if the values meet
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 vary
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 the
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't
understand the syntax...

Anybody got a clue?
Thanks for your help,

Titus.


--
titushanke
------------------------------------------------------------------------
titushanke's Profile:
http://www.excelforum.com/member.php...o&userid=19997
View this thread: http://www.excelforum.com/showthread...hreadid=519286

SUMPRODUCT with PLACEHOLDERS?
 

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.


--
titushanke
------------------------------------------------------------------------
titushanke's Profile: http://www.excelforum.com/member.php...o&userid=19997
View this thread: http://www.excelforum.com/showthread...hreadid=519286

SUMPRODUCT with PLACEHOLDERS?
 

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)

"titushanke" wrote
in message ...

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.


--
titushanke
------------------------------------------------------------------------
titushanke's Profile:
http://www.excelforum.com/member.php...o&userid=19997
View this thread: http://www.excelforum.com/showthread...hreadid=519286