=sumproduct in 2 bestanden

[Bandito]

Beste allemaal,

Ik heb een bestand met daarin kolommen waar ik verschillende gegevens invul. Hiervan zijn de datum en de "x" in kolom G en H het belangrijkste.

In een ander bestand(file1.xlsx) wil ik graag laten zien in welke rijen er in zowel G als H een "x" staat.
Dit wordt gesorteerd per maand. Wanneer er dus een datum in januari wordt ingevuld en in zowel G als H een "x" staat zou ik graag in een ander bestand (NEW.xlsm) een " 1" willen zien.

Ik heb via via een formule gekregen die dit zou moeten kunnen maar hij werkt helaas niet. Ik krijg als resultaat 0 terwijl het resultaat in dit voorbeeld 2 zou moeten zijn.

De code die in het bestand NEW.xlsm in G4 het resultaat 1 zou moeten geven (alleen in regel 2 staan 2x "x");

=SUMPRODUCT(--(TEXT('[file1.xlsx]Data Gel'!D$2:D$10000;"[$-Nl-NL]mmmm")=A4)*('[file1.xlsx]Data Gel'!D$2:D$10000>0)*(('[file1.xlsx]Data Gel'!G$2:G$10000="x")*('[file1.xlsx]Data Gel'!H$2:H$10000="x")))

Weet iemand wat hier mis gaat?
Hopelijk is mijn vraag met bijgevoegde bestandjes duidelijk genoeg.

 

[AlexCEL]

Zo wel goed?

[EN] 
=SUMPRODUCT(('[file1.xlsx]Data Gel'!$D$2:$D$10000>0)*(MONTH('[file1.xlsx]Data Gel'!$D$2:$D$10000)=ROW(A1))*('[file1.xlsx]Data Gel'!$G$2:$G$10000="x")*('[file1.xlsx]Data Gel'!$H$2:$H$10000="x"))

[NL] 
=SOMPRODUCT(('[file1.xlsx]Data Gel'!$D$2:$D$10000>0)*(MAAND('[file1.xlsx]Data Gel'!$D$2:$D$10000)=RIJ(A1))*('[file1.xlsx]Data Gel'!$G$2:$G$10000="x")*('[file1.xlsx]Data Gel'!$H$2:$H$10000="x"))

 

[Bandito]

Even dacht ik ja! maar helaas.

In de formulier wordt verwezen naar A1 als maand, hier staat echter het jaartal 2018.

(MONTH('[file1.xlsx]Data Gel'!$D$2:$D$10000)=ROW(A1))

De formule werkt nu wel. Echter wil ik dat hij per maand selecteert en niet per jaar.

Wanneer ik A1 verander in A4 (januari) werkt de formule ineens niet meer.

 

[HSV]

De formule lees je verkeerd.

Month('[file1.xlsx]Data Gel'!$D$2:$D$10000)=[COLOR=#ff0000]RIJ(A1)[/COLOR])

Die lees je als:

Month('[file1.xlsx]Data Gel'!$D$2:$D$10000)=[COLOR=#ff0000]1[/COLOR])

Oftewel:
Month('[file1.xlsx]Data Gel'!$D$2:$D$10000)=januari)

De formule kijkt verder niet wat er in cel A1 staat.

 

[Bandito]

Verklaard al iets meer.

Wanneer ik de formulie aanpas krijg ik het resultaat; #NAME?

Waarschijnlijk omdat in Month('[file1.xlsx]Data Gel'!$D$2:$D$10000) datums staan en niet de waarde januari ?

 

[HSV]

Het resultaat is hier goed.
Waar ik rij(a1) schreef moet bij jou Row(a1) zijn natuurlijk.

Januari was geschreven om het jou duidelijk te maken.

Welke uitkomst verwacht je van de formule?

 

[Bandito]

Ik verwacht dat de formule in file1.xlxs kijkt welke data er ingevoerd is in welke maand. In het voorbeeld wat bijgevoegd is zie je bijvoorbeeld dat er 2 regels in januari ingevuld zijn. Deze datums staan in de kolom D.

Volgens mij zou dit stukje van de formule moeten doen;

Month('[file1.xlsx]Data Gel'!$D$2:$D$10000)=ROW(A1))

Dit werkt dus niet want in A1 staat 2018 en als ik dit verander in A4 krijg ik als resultaat "0", dit terwijl het resultaat "1" zou moeten zijn (zie verder bericht).


Het vervolg van de formulie moet kijken of er bij die datums in zowel G als in H in file1.xlsx een "x" staat. Is dat zo dan geeft de formulie in dit voorbeeld een "1". Wanneer er meer datums in januari met een "x" in G en H komen wordt dit natuurlijk "2" enz.

Kort gezegd; Ik wil eigenlijk alle rijen tellen waar in G en H een kruisje staat gesorteerd op maand.

 

[HSV]

De formule verwijst niet naar wat er in cel A1 staat zoals ik al vermelde.
Je mag in cel A1 alles zetten wat er op je toetsenbord te vinden is.
De formule doet daar niets mee.

De formule werkt prima hier.
Plak de formule in G4 van NEW.xlsm.
Het resultaat is 1.
Zet ik een kruisje in file1.xlsx cel G3 wordt het resultaat 2.

 

[Bandito]

De formulie werkt bij mij ook zoals die is maar wanneer ik de formule voor februari (G5) in NEW.xlsm wil gebruiken ontstaat de verwarring en volgens mij de fout.

 

[HSV]

Geeft als resultaat 0.

 

[Bandito]

Verhip, het werkt!
Morgen nog maar eens goed naar kijken. Voor nu hartelijk dank!

 

[Bandito]

Ik ben er mee aan het spelen geweest en weet eigenlijk nog steeds niet precies hoe het volgende werkt;

(MONTH('C:\Users\Rob.Holleman\Desktop\[Registratie review.xlsx]Data Gel'!$D$2:$D$10000)=ROW(A1)

In andere cellen gebruik ik;

=SUMPRODUCT(--(TEXT('C:\Users\Rob.Holleman\Desktop\[Registratie review.xlsx]Data Gel'!D$2:D$10000;"mmmm")=A3)

Dit snap ik wel. In A3 staat Januari dus hij kijkt in welke regels allemaal een datum in Januari staat.

Dit geldt niet voor de bovenste formule.

Ik gebruik bijvoorbeeld een listbox om een maand te selecteren. Met de onderste formule krijg ik dan mooie resultaten maar met de bovenste niet.
Ook wanneer ik een regel toevoeg onder regel 1 dan gaat de bovenste formule fout.

Hoe precies wordt bij

(MONTH('C:\Users\Rob.Holleman\Desktop\[Registratie review.xlsx]Data Gel'!$D$2:$D$10000)=ROW(A1)

de maand bepaald?

 

[AlexCEL]

Hele discussie geweest zie ik...

ik vond je methode om de maandnamen met elkaar te vergelijken nogal omslachtig en foutgevoelig. Denk dat het mede daarom niet goed ging. Maar ok.

De functie MAAND/MONTH geeft bij een datum het nummer van de maand van die datum. Dus MAAND("1-1-2018")=1 of MONTH("20-3-2014")=3.
De functie RIJ/ROW geeft ook een getal. RIJ(A3)=3, ROW(Q27)=27, geheel onafhankelijk van wat er in die cel staat. Alleen het RIJ-nummer wordt weergegeven.
Dit heb ik gedaan zodat je de formule kunt doortrekken naar onderen. Je krijgt dan in de kolom een serie 1,2,3,4,5 etc. Oftewel januari, februari, maart, ...
Het mooie is dat het nu ook niet meer uitmaakt welke taal-versie van excel je hebt.

 

[HSV]

Pak het stukje over van cel B4, stop het in de andere formule en klaar ben je.

--(TEKST('[file1.xlsx]Data Gel'!D$2:D$10000;"mmmm")=A4)