sumproduct problem

wrz

New Member
Joined
Nov 16, 2005
Messages
3
Hey, the problem is that i want to use sumproduct based on 2 ranges to search from third range, the problem is that in the third range are codes which include text strings : TT9298473 so in the 1-st range are car registration numbers and in the second range are dates, if they match the formula should output the corresponding code from 3-rd range

Is there a way that sumproduct accepts text strings ???



My current formula looks like this :
=SUMPRODUCT(--(Kütusetabel!$C$3:$C$200=$A$1);--(Kütusetabel!$D$3:$D$200=$A4);(Kütusetabel!$K$3:$K$200))

Would someone PLEASE post a solution I really need the solution
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Hello, wrz
Welcome to the Board !!!!!

sumproduct is not what you need to my sense

it this what you want ?
TABLE
Code:
A1  B1  C1
A2  B2  C2
A2  B3  C3
input A2 B3
output
C3

if not pleasepost a sample of your sheet using Colo's HTML Maker utility for displaying your Excel Worksheet on the board

On the "Excel Questions" main page there is a link to HTML Maker FAQ. You'll find out how to post correctly.
On the main page of the Forum there is a link to a Test here forum and a link to About this Board - to ask questions about HTLMMaker ...

or at least provide a little table + input + output

kind regards,
Erik
 
Upvote 0
OK i cut out part of the ranges which are in my table, what i need is excel to find the corresponding code from third column, for example: while the reg. nr. is 046TLL and the date is 2.jan.2005 . I used sumproduct beacause it is able to go through all the rows in the column because the dates and reg numbers will be mixed up in the future and monthly reports for each reg. nr. must be created. I managed to sort out all the other parameters I need but I'm stuck on this one because of the text strings in the bginning of the code
HELP ....

*Removed by Admin*
 
Upvote 0
I suppose you posted a sample ?
can't see anything then a corrupted page
see if some moderator can fix it (I reported your post) unless you try again
please read my post again, you received some links: one is the "test"-forum where you can try out your htmlmaker-code

best regards,
Erik
 
Upvote 0
wrz said:
Hey, the problem is that i want to use sumproduct based on 2 ranges to search from third range, the problem is that in the third range are codes which include text strings : TT9298473 so in the 1-st range are car registration numbers and in the second range are dates, if they match the formula should output the corresponding code from 3-rd range

Is there a way that sumproduct accepts text strings ???



My current formula looks like this :
=SUMPRODUCT(--(Kütusetabel!$C$3:$C$200=$A$1);--(Kütusetabel!$D$3:$D$200=$A4);(Kütusetabel!$K$3:$K$200))

Would someone PLEASE post a solution I really need the solution

=INDEX(Kütusetabel!$K$3:$K$200;MATCH(1;(Kütusetabel!$C$3:$C$200=$A$1)*(Kütusetabel!$D$3:$D$200=$A4);0))

which you need to confirm with control+shift+enter, not just with enter.

Edit: Changed comma's to semi-colon.
 
Upvote 0
Hi, Aladin,
Wrz (OP) didn't confirm my table was OK :confused:
little typo ";" should be "," else I'm missing something
=INDEX(Kütusetabel!$K$3:$K$200,MATCH(1,(Kütusetabel!$C$3:$C$200=$A$1)*(Kütusetabel!$D$3:$D$200=$A4),0))

is this a good alternative or is it slower :)
(I like this syntax)
=INDEX(Kütusetabel!$K$3:$K$200,MATCH($A$1&$A4,Kütusetabel!$C$3:$C$200&Kütusetabel!$D$3:$D$200,0))
(confirm with Control-Shift-Enter)

best regards,
Erik
 
Upvote 0
Press Enter

=LOOKUP(2,1/((Kütusetabel!$C$3:$C$200=$A$1)*(Kütusetabel!$D$3:$D$200=$A4)),Kütusetabel!$K$3:$K$200)
 
Upvote 0
for your information
Code:
A1  B1  C1 
A2  B2  C2 
A2  B3  C3
A2  B2  C4
INPUT A2 B2
when there are several matches
vane0326's formula is returning the last occurence : C4
the other formulas will return the first occurence : C2

that's at least my experience
best regards,
Erik
 
Upvote 0
Hi erik.van.geit,

The formula I provided does not grab the Last value it works excatly the same of your formulas and Aladin's But mine is not array. :)

=LOOKUP(2,1/((A1:A4=B9)*(B1:B4=C9)),C1:C4)
Book99999.xls
ABCD
1A1B1C1
2A2B2C2
3A2B3C3
4A2B2C4
5
6
7
8
9A1B1
10A2B3
11
12C1C3
13
Sheet1
 
Upvote 0
Hey vane0326,

it's a nice formula but I agree with erik. The examples you give only have one match, if there are multiple matches it picks the last.

In some cases I guess that will be the requirement, in others not
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,148
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top