Need help with Reference formula within 2 Spreadsheets

[LarissaM711]

Hello,

i have two sheets called "Hubspot Kontakte" and "Hubspot Lifecycle". I Want to combine the data and automatically insert the Number of contacts from "Hubspot Lifecycle" into the empty Cells in "Hubspot Kontakte", if the date is matching and the lifecycle-Names are fitting. (Excel Attached)

Name in Lifecycle / Name in "Hubspot Kontakte":

lead = Lead

marketingqualifiedlead = MQL

salesqualifiedlead = SQL

140592587 = Akqusition

[#2]

@LarissaM711 

See the attached version.

[#3]

Hello Hans, thank you for your help again!
B2 "Contacs" should not be touched, but that's no problem :)
Is there a way to format the different columns, so it's a automatic formular? Your Formular itself works perfect, it's just that this sheet will be automatically be updated for the upcoming Days, so it would be nice to not have to copy the formular in each row manually

[#4]

@LarissaM711 

You can change 'Hubspot Lifecycle'!$C$2:$C$635 to 'Hubspot Lifecycle'!$C$2:$C$10000 in the formulas, and similar for the other ranges. that way, dates added in the future will be taken into account.

You can copy the formulas on the Hubspot Kontakte sheet down as far as you want; they will return 0 if column A is empty (or in the future)