Got stuck with SUMPRODUCT - multiple criteria (text/numbers)

MadsHusted · Oct 3, 2018

Hi,

I'm working with a SUMPRODUCT formula to sum the total amount of occurrences in my workbook (+3800 lines).

The criteria are:
Array 1; if cell value matches other cell
Array 2; if cell value matches "text"

Either I get #N/A or 0.. I suspect it's related to formating issues but neither TRIM(), TEXT() nor formatting to General works.. I've tried to add ISNUMBER but it returns #VALUE !

I would appreciate any inputs. Let me know if you can't open the file.

Thank you.

Link to OneDrive: https://1drv.ms/x/s!AjXp0Ya_hoO0hmAmQRUzvSGhXNmu

MARZIOTULLIO · Oct 3, 2018

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A

[/TD]
[TD]

B

[/TD]
[TD]

C

[/TD]
[TD]

D

[/TD]
[/TR]
[TR]
[TD]

1

[/TD]
[TD]Article code[/TD]
[TD]Product ABC code[/TD]
[TD]Supplier code[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2

[/TD]
[TD]2000F34821422[/TD]
[TD]CA[/TD]
[TD]15837[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3

[/TD]
[TD]B43108A1[/TD]
[TD]BB[/TD]
[TD]15837[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4

[/TD]
[TD]32737CRC[/TD]
[TD]N[/TD]
[TD]54630[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5

[/TD]
[TD]AGLA403011P001[/TD]
[TD]CA[/TD]
[TD]15837[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6

[/TD]
[TD]SKPF12C08N[/TD]
[TD]BB[/TD]
[TD]15837[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7

[/TD]
[TD]KRLP7KRP006[/TD]
[TD]BA[/TD]
[TD]15837[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8