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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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
  • Locked
  • Question Question
IF + VLOOKUP return result within date range
Replies
3
Views
70
Joe4
Joe4
F
  • Question Question
VBA Macro Issue: Replacing Simple Formula with Complex Formula Causes Error
Replies
2
Views
126
flashgordie
F
S
  • Question Question
Horizontal Return
Replies
6
Views
230
Fluff
Fluff
S
  • Question Question
Layered return values in Excel sheet
Replies
4
Views
123
lrobbo314
lrobbo314
P
  • Question Question
Populating, aggregating and sorting multiple matching values in Excel
Replies
0
Views
220
Pbobas
P

Forum statistics

Threads
1,224,824
Messages
6,181,187
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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