SOLVED

SUMMENPRODUKT mit Datum nach Monat filtern

%3CLINGO-SUB%20id%3D%22lingo-sub-1682288%22%20slang%3D%22de-DE%22%3EFilter%20TOTAL%20PRODUCT%20with%20date%20by%20month%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1682288%22%20slang%3D%22de-DE%22%3E%3CP%3EHello%2C%20%3CBR%20%2F%3E%20I%20try%20to%20filter%20certain%20values%20from%20a%20record%20by%20the%20date%20of%20the%20transaction%3B%20%3CBR%20%2F%3E%20I%20use%20the%20formula%20%22%3DSUMMENPRODUKT(('JOURNAL%20Holvi'!%20G%3AG%3D%22EBAY%20GMBH%22)*('JOURNAL%20Holvi'!%20A%3AA%3D%22%3F%3F.%2001.19*%22)*('JOURNAL%20Holvi'!%20L%3AL))%22%20%3CBR%20%2F%3E%20in%20G%3AG%20are%20the%20vendors%20names%3B%20inL%3AL%20the%20transaction%20total%20and%20in%20A%3AA%20the%20date%20of%20the%20transaction.%20%3CBR%20%2F%3E%20when%20I%20try%20to%20filter%20here%20after%20January%202019%2C%20only%20%220%22%20comes%20out%20as%20a%20result%2C%20but%20there%20is%20definitely%20a%20transaction%20here.%20%3CBR%20%2F%3E%20Below%20are%20a%20few%20cells%20from%20A%3AA%20%3CBR%20%2F%3E%20%22%3C%2FP%3E%3CTABLE%20border%3D%220%22%20width%3D%22125%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22125%22%20height%3D%2221%22%3E09.01.19%2C%2020%3A05%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2221%22%3E10.01.19%2C%2016%3A23%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2221%22%3E10.01.19%2C%2016%3A25%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2221%22%3E11.01.19%2C%2011%3A01%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2221%22%3E11.01.19%2C%2011%3A01%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CTABLE%20border%3D%220%22%20width%3D%22125%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22125%22%20height%3D%2221%22%3E26.03.19%2C%2002%3A15%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CTABLE%20border%3D%220%22%20width%3D%22125%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22125%22%20height%3D%2221%22%3E01.04.19%2C%2017%3A23%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2221%22%3E01.04.19%2C%2017%3A23%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2221%22%3E01.04.19%2C%2017%3A23%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%22%20%3CBR%20%2F%3E%20Greetings%20%3CBR%20%2F%3E%20Sebastian%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1682288%22%20slang%3D%22de-DE%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1682344%22%20slang%3D%22en-US%22%3ERe%3A%20SUMMENPRODUKT%20mit%20Datum%20nach%20Monat%20filtern%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1682344%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F798403%22%20target%3D%22_blank%22%3E%40Sebastian666%3C%2FA%3E%26nbsp%3BTry%20it%20this%20way%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUMMENPRODUKT(ISTZAHL(SUCHEN(%2201.19%2C%22%3BA%3AA))*(G%3AG%3D%22EBAY%20GMBH%22)*L%3AL)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hallo,
ich versuche aus einem Datensatz gewisse Werte nach dem Datum der Transaktion zu filtern;
ich nutze hierfür die Formel "=SUMMENPRODUKT(('JOURNAL Holvi'!G:G="EBAY GMBH")*('JOURNAL Holvi'!A:A="??.01.19*")*('JOURNAL Holvi'!L:L))"
in G:G sind die Lieferanten Namen; inL:L die Transaktionssumme und in A:A das Datum der Transaktion.
bei meinem Versuch hier nach dem Januar 2019 zu filtern kommt nur "0" als Ergebnis raus, es gibt hier aber definitiv eine Transaktion.
Nachfolgend noch ein paar Zellen aus A:A
"

09.01.19, 20:05
10.01.19, 16:23
10.01.19, 16:25
11.01.19, 11:01
11.01.19, 11:01
26.03.19, 02:15
01.04.19, 17:23
01.04.19, 17:23
01.04.19, 17:23

"
Liebe Grüße 
Sebastian

3 Replies
Highlighted
Best Response confirmed by Sebastian666 (New Contributor)
Solution

@Sebastian666 Try it this way:

=SUMMENPRODUKT(ISTZAHL(SUCHEN("01.19,";A:A))*(G:G="EBAY GMBH")*L:L)

 

Highlighted
Danke!!! Für die schnelle und richtige Lösung:)
Highlighted

@Sebastian666 You're welcome!