Formula errors?

A

Aberdham

Board Regular
Joined
Mar 8, 2018
Messages
163
Office Version
  1. 365
Platform
  1. Windows
Hi,

Could anyone take a look into the formula for me please?

It seems something went wrong, and all the result Returns to be "0"

SUMPRODUCT('Lease-Sales'!$G$462:$G$579*ISNUMBER(FIND(""&"RD"&"";'Lease-Sales'!$B$462:$B$579))-'Lease-Sales'!$G$462:$G$579*ISNUMBER(FIND(""&"RC"&"";'Lease-Sales'!$B$462:$B$579));('Lease GP April'!$B3='Lease-Sales'!$A$462:$A$579);(C$3=LEFT('Lease-Sales'!$D$462:$D$579;LEN(C$3))))


Your assistance will be greatly appreciated !!
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
Not sure what you are trying to do but you need to convert (coerce) the logical values in the comparisons to 0 or 1.
I recommend the double unary-operator --, that is:
--('Lease GP April'!$B3='Lease-Sales'!$A$462:$A$579)
and
--(C$3=LEFT('Lease-Sales'!$D$462:$D$579;LEN(C$3)))

Hope this helps

M.
 
Upvote 0
Marcelo Branco said:
Not sure what you are trying to do but you need to convert (coerce) the logical values in the comparisons to 0 or 1.
I recommend the double unary-operator --, that is:
--('Lease GP April'!$B3='Lease-Sales'!$A$462:$A$579)
and
--(C$3=LEFT('Lease-Sales'!$D$462:$D$579;LEN(C$3)))

Hope this helps

M.
Click to expand...

Thanks for your reply! So I have These 3 tables:

[TABLE="width: 801"]
<tbody>[TR]
[TD]Table 1 [/TD]
[TD] Revenue[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SN[/TD]
[TD]Invoice no.[/TD]
[TD]Customer[/TD]
[TD]description[/TD]
[TD]amount Dollar[/TD]
[TD]exchange rate[/TD]
[TD]amount euro[/TD]
[/TR]
[TR]
[TD]158958[/TD]
[TD]15RD000268[/TD]
[TD]ASD[/TD]
[TD]process_fee_23.04-22.05.2017_SN_158958[/TD]
[TD] $ 38.850,00[/TD]
[TD] $ 1,2361[/TD]
[TD] € 31.429,50[/TD]
[/TR]
[TR]
[TD]158958[/TD]
[TD]15MD000158[/TD]
[TD]ASD[/TD]
[TD]registration_fee_01.03-31.03.2017_ESN_158958[/TD]
[TD] $ 57.722,00[/TD]
[TD] $ 1,2323[/TD]
[TD] € 46.840,87[/TD]
[/TR]
[TR]
[TD]158958[/TD]
[TD]15RC000158[/TD]
[TD]ASD[/TD]
[TD]process_fee_23.04-22.05.2017_ESN_158958[/TD]
[TD] $ 2.102,00[/TD]
[TD] $ 1,2323[/TD]
[TD] € 1.705,75[/TD]
[/TR]
[TR]
[TD]158958[/TD]
[TD]15MC000159[/TD]
[TD]ASD[/TD]
[TD]registration_fee_01.03-31.03.2017_ESN_158958[/TD]
[TD] $ 5.541,00[/TD]
[TD] $ 1,2323[/TD]
[TD] € 4.496,47[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col></colgroup>[/TABLE]


[TABLE="width: 803"]
<tbody>[TR]
[TD]Table 2[/TD]
[TD]Cost[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SN[/TD]
[TD]Invoice no.[/TD]
[TD]Customer[/TD]
[TD]description[/TD]
[TD]amount Dollar[/TD]
[TD]exchange rate[/TD]
[TD]amount euro[/TD]
[/TR]
[TR]
[TD]158958[/TD]
[TD]SAEL-18-0036-04[/TD]
[TD]ASD[/TD]
[TD]process_fee_10.04-09.05.2017_SN_158958[/TD]
[TD] $ 37.233,49[/TD]
[TD] $ 1,2308[/TD]
[TD] € 30.251,45[/TD]
[/TR]
[TR]
[TD]158958[/TD]
[TD]200-PANIN-000058[/TD]
[TD]ASD[/TD]
[TD]registration_fee_01.03-31.03.2017_SN_158958[/TD]
[TD] $ 82.483,14[/TD]
[TD] $ 1,2321[/TD]
[TD] € 66.945,17[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col></colgroup>[/TABLE]

[TABLE="width: 603"]
<tbody>[TR]
[TD]Table 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SN[/TD]
[TD]PF (Revenue)[/TD]
[TD]RF
(Revenue)[/TD]
[TD]PF
(cost)[/TD]
[TD]RF
(cost)[/TD]
[/TR]
[TR]
[TD]158958[/TD]
[TD] € 29.723,74[/TD]
[TD] € 42.344,40[/TD]
[TD] € 30.251,45[/TD]
[TD] € 66.945,17[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col></colgroup>[/TABLE]

Table 1 and Table 2 are the Output tables and table 3 is the Input table

PF and RF in table 3 are taken from table 1
and the PF and RF from the cost side is extracted from table 2

and the formula i am using now is:
=SUMPRODUCT('Lease-Sales'!$G$462:$G$579*ISNUMBER(FIND("RD";'Lease-Sales'!$B$462:$B$579))-'Lease-Sales'!$G$462:$G$579*ISNUMBER(FIND("RC";'Lease-Sales'!$B$462:$B$579));--('Lease GP April'!$B9='Lease-Sales'!$A$462:$A$579);--(C$2=LEFT('Lease-Sales'!$D$462:$D$579;LEN(C$2))))

and return to "0"

can you please tell me what went wrong? Thank you.
 
Upvote 0
I think you meant Table 1 and Table 2 are the Input tables (given) and table 3 is the Output table (desired results)

Maybe something like this...


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[TD="bgcolor: #DCE6F1"]
F​
[/TD]
[TD="bgcolor: #DCE6F1"]
G​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]
Table 1​
[/TD]
[TD]
Revenue​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
SN​
[/TD]
[TD]
Invoice no.​
[/TD]
[TD]
Customer​
[/TD]
[TD]
description​
[/TD]
[TD]
amount Dollar​
[/TD]
[TD]
exchange rate​
[/TD]
[TD]
amount euro​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD]
158958​
[/TD]
[TD]
15RD000268​
[/TD]
[TD]
ASD​
[/TD]
[TD]
process_fee_23.04-22.05.2017_SN_158958​
[/TD]
[TD]
38.850,00​
[/TD]
[TD]
1,2361​
[/TD]
[TD]
31.429,50​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD]
158958​
[/TD]
[TD]
15MD000158​
[/TD]
[TD]
ASD​
[/TD]
[TD]
registration_fee_01.03-31.03.2017_ESN_158958​
[/TD]
[TD]
57.722,00​
[/TD]
[TD]
1,2323​
[/TD]
[TD]
46.840,87​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD]
158958​
[/TD]
[TD]
15RC000158​
[/TD]
[TD]
ASD​
[/TD]
[TD]
process_fee_23.04-22.05.2017_ESN_158958​
[/TD]
[TD]
2.102,00​
[/TD]
[TD]
1,2323​
[/TD]
[TD]
1.705,75​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD]
158958​
[/TD]
[TD]
15MC000159​
[/TD]
[TD]
ASD​
[/TD]
[TD]
registration_fee_01.03-31.03.2017_ESN_158958​
[/TD]
[TD]
5.541,00​
[/TD]
[TD]
1,2323​
[/TD]
[TD]
4.496,47​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
9​
[/TD]
[TD]
Table 2​
[/TD]
[TD]
Cost​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
10​
[/TD]
[TD]
SN​
[/TD]
[TD]
Invoice no.​
[/TD]
[TD]
Customer​
[/TD]
[TD]
description​
[/TD]
[TD]
amount Dollar​
[/TD]
[TD]
exchange rate​
[/TD]
[TD]
amount euro​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
11​
[/TD]
[TD]
158958​
[/TD]
[TD]
SAEL-18-0036-04​
[/TD]
[TD]
ASD​
[/TD]
[TD]
process_fee_10.04-09.05.2017_SN_158958​
[/TD]
[TD]
37.233,49​
[/TD]
[TD]
1,2308​
[/TD]
[TD]
30.251,45​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
12​
[/TD]
[TD]
158958​
[/TD]
[TD]
200-PANIN-000058​
[/TD]
[TD]
ASD​
[/TD]
[TD]
registration_fee_01.03-31.03.2017_SN_158958​
[/TD]
[TD]
82.483,14​
[/TD]
[TD]
1,2321​
[/TD]
[TD]
66.945,17​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
13​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
14​
[/TD]
[TD]
Table 3​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
15​
[/TD]
[TD]
SN​
[/TD]
[TD]
PF (Revenue)​
[/TD]
[TD]
RF (revenue)​
[/TD]
[TD]
PF (cost)​
[/TD]
[TD]
RF (cost)​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
16​
[/TD]
[TD]
158958​
[/TD]
[TD]
29.723,74​
[/TD]
[TD]
42.344,40​
[/TD]
[TD]
30.251,45​
[/TD]
[TD]
66.945,17​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Values in blue
Formula in B16
=SUMPRODUCT(--($A$3:$A$6=A16),ISNUMBER(SEARCH("RD",$B$3:$B$6))*$G$3:$G$6-ISNUMBER(SEARCH("RC",$B$3:$B$6))*$G$3:$G$6)

Formula in C16
=SUMPRODUCT(--($A$3:$A$6=A16),ISNUMBER(SEARCH("MD",$B$3:$B$6))*$G$3:$G$6-ISNUMBER(SEARCH("MC",$B$3:$B$6))*$G$3:$G$6)

It's not clear what are the criteria to get the values in red (D16 and E16). Could you clarify?

M.
 
Upvote 0
Marcelo Branco said:
I think you meant Table 1 and Table 2 are the Input tables (given) and table 3 is the Output table (desired results)

Maybe something like this...


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[TD="bgcolor: #DCE6F1"]
F​
[/TD]
[TD="bgcolor: #DCE6F1"]
G​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]
Table 1​
[/TD]
[TD]
Revenue​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
SN​
[/TD]
[TD]
Invoice no.​
[/TD]
[TD]
Customer​
[/TD]
[TD]
description​
[/TD]
[TD]
amount Dollar​
[/TD]
[TD]
exchange rate​
[/TD]
[TD]
amount euro​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD]
158958​
[/TD]
[TD]
15RD000268​
[/TD]
[TD]
ASD​
[/TD]
[TD]
process_fee_23.04-22.05.2017_SN_158958​
[/TD]
[TD]
38.850,00​
[/TD]
[TD]
1,2361​
[/TD]
[TD]
31.429,50​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD]
158958​
[/TD]
[TD]
15MD000158​
[/TD]
[TD]
ASD​
[/TD]
[TD]
registration_fee_01.03-31.03.2017_ESN_158958​
[/TD]
[TD]
57.722,00​
[/TD]
[TD]
1,2323​
[/TD]
[TD]
46.840,87​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD]
158958​
[/TD]
[TD]
15RC000158​
[/TD]
[TD]
ASD​
[/TD]
[TD]
process_fee_23.04-22.05.2017_ESN_158958​
[/TD]
[TD]
2.102,00​
[/TD]
[TD]
1,2323​
[/TD]
[TD]
1.705,75​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD]
158958​
[/TD]
[TD]
15MC000159​
[/TD]
[TD]
ASD​
[/TD]
[TD]
registration_fee_01.03-31.03.2017_ESN_158958​
[/TD]
[TD]
5.541,00​
[/TD]
[TD]
1,2323​
[/TD]
[TD]
4.496,47​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
9​
[/TD]
[TD]
Table 2​
[/TD]
[TD]
Cost​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
10​
[/TD]
[TD]
SN​
[/TD]
[TD]
Invoice no.​
[/TD]
[TD]
Customer​
[/TD]
[TD]
description​
[/TD]
[TD]
amount Dollar​
[/TD]
[TD]
exchange rate​
[/TD]
[TD]
amount euro​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
11​
[/TD]
[TD]
158958​
[/TD]
[TD]
SAEL-18-0036-04​
[/TD]
[TD]
ASD​
[/TD]
[TD]
process_fee_10.04-09.05.2017_SN_158958​
[/TD]
[TD]
37.233,49​
[/TD]
[TD]
1,2308​
[/TD]
[TD]
30.251,45​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
12​
[/TD]
[TD]
158958​
[/TD]
[TD]
200-PANIN-000058​
[/TD]
[TD]
ASD​
[/TD]
[TD]
registration_fee_01.03-31.03.2017_SN_158958​
[/TD]
[TD]
82.483,14​
[/TD]
[TD]
1,2321​
[/TD]
[TD]
66.945,17​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
13​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
14​
[/TD]
[TD]
Table 3​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
15​
[/TD]
[TD]
SN​
[/TD]
[TD]
PF (Revenue)​
[/TD]
[TD]
RF (revenue)​
[/TD]
[TD]
PF (cost)​
[/TD]
[TD]
RF (cost)​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
16​
[/TD]
[TD]
158958​
[/TD]
[TD]
29.723,74​
[/TD]
[TD]
42.344,40​
[/TD]
[TD]
30.251,45​
[/TD]
[TD]
66.945,17​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Values in blue
Formula in B16
=SUMPRODUCT(--($A$3:$A$6=A16),ISNUMBER(SEARCH("RD",$B$3:$B$6))*$G$3:$G$6-ISNUMBER(SEARCH("RC",$B$3:$B$6))*$G$3:$G$6)

Formula in C16
=SUMPRODUCT(--($A$3:$A$6=A16),ISNUMBER(SEARCH("MD",$B$3:$B$6))*$G$3:$G$6-ISNUMBER(SEARCH("MC",$B$3:$B$6))*$G$3:$G$6)

It's not clear what are the criteria to get the values in red (D16 and E16). Could you clarify?

M.
Click to expand...

The red ones are from table 2
G11 and G12 respectively,
however, there's no define rules for invoice number such as the ones in table 1
 
Upvote 0
Aberdham said:
The red ones are from table 2
G11 and G12 respectively,
however, there's no define rules for invoice number such as the ones in table 1
Click to expand...

If there are no rules to follow how can one create a formula to get the proper value?

M.
 
Upvote 0
Marcelo Branco said:
If there are no rules to follow how can one create a formula to get the proper value?

M.
Click to expand...

To link the SN and the description?

since in table 3 the PF and RF stands for process fee and registration fee respectively.
Perhaps to use a recognition and sum product or aggregate would do the job?

would be great if you could help.
Thanks!
 
Upvote 0
Aberdham said:
To link the SN and the description?

since in table 3 the PF and RF stands for process fee and registration fee respectively.
Click to expand...

Try
D16
=SUMPRODUCT(--($A$11:$A$12=A16),--ISNUMBER(SEARCH("process",$D$11:$D$12)),$G$11:$G$12)

E16
=SUMPRODUCT(--($A$11:$A$12=A16),--ISNUMBER(SEARCH("registration",$D$11:$D$12)),$G$11:$G$12)

M.
 
Upvote 0
Marcelo Branco said:
If there are no rules to follow how can one create a formula to get the proper value?

M.
Click to expand...

and at the end of the description, there is always an indication of _SN xxxxxx
so perhaps to satisfy the description as PF(process fee) or RF (registration fee) as well as the SN
we have the formula?
 
Upvote 0
Aberdham said:
and at the end of the description, there is always an indication of _SN xxxxxx
so perhaps to satisfy the description as PF(process fee) or RF (registration fee) as well as the SN
we have the formula?
Click to expand...

See post 8

M.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Horizontal Return
Replies
6
Views
224
Fluff
Fluff
S
  • Question Question
Layered return values in Excel sheet
Replies
4
Views
100
lrobbo314
lrobbo314
P
  • Question Question
Populating, aggregating and sorting multiple matching values in Excel
Replies
0
Views
199
Pbobas
P
D
  • Solved
Formula for 10 top by department, but only showing results greater than 0
Replies
3
Views
136
Fluff
Fluff
k3yn0t3
  • Question Question
Formula to Sum Based on Criteria
2
Replies
19
Views
1K
k3yn0t3
k3yn0t3

Forum statistics

Threads
1,223,911
Messages
6,175,327
Members
452,635
Latest member
laura12345

Share this page

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