Vlookup sumproduct?

[Bonebeast]

Hello!

I have managed to use the sumproduct formula in order to count the text "telefon" and "1/2 telefon" within a certain date.
SUMPRODUCT(('v38-41'!$E$3:$FH$3>=Statistik!$A$3)*('v38-41'!$E$3:$FH$3<=Statistik!$B$3)*('v38-41'!$E7:$FH7="telefon"))+SUMPRODUCT(('v38-41'!$E$3:$FH$3>=Statistik!$A$3)*('v38-41'!$E$3:$FH$3<=Statistik!$B$3)*('v38-41'!$E7:$FH7="1/2 telefon")*0,5)

However, I want it the formula to find the name within the column and then proceed to calculate the "telefon" and "1/2 telefon". For instance to look up "John" or "D4" in the column D of the differet sheet.
Is that possible?

 

[Fluff]

Hi & welcome to MrExcel.
How about like

SUMPRODUCT(('v38-41'!$E$3:$FH$3>=Statistik!$A$3)*('v38-41'!$E$3:$FH$3<=Statistik!$B$3)*('v38-41'!$D$5:$D$10=D6)*('v38-41'!$E$5:$FH$10="telefon"))

 

[Bonebeast]

Hi & welcome to MrExcel.
How about like

SUMPRODUCT(('v38-41'!$E$3:$FH$3>=Statistik!$A$3)*('v38-41'!$E$3:$FH$3<=Statistik!$B$3)*('v38-41'!$D$5:$D$10=D6)*('v38-41'!$E$5:$FH$10="telefon"))

It worked, thank you so much!

Is there a more simple way to add in "1/2 telefon" and make each cell count as 0,5 other than adding +SUMPRODUCT(('v38-41'!$E$3:$FH$3>=Statistik!$A$3)*('v38-41'!$E$3:$FH$3<=Statistik!$B$3)*('v38-41'!$E7:$FH7="1/2 telefon")*0,5) like I have done?

 

[Fluff]

Try

=SUMPRODUCT(('v38-41'!$E$3:$FH$3>=Statistik!$A$3)*('v38-41'!$E$3:$FH$3<=Statistik!$B$3)*('v38-41'!$D$5:$D$10=D6)*(('v38-41'!$E$5:$FH$10="telefon")+('v38-41'!$E$5:$FH$10="1/2 telefon")*.5))

 

[Bonebeast]

Try

=SUMPRODUCT(('v38-41'!$E$3:$FH$3>=Statistik!$A$3)*('v38-41'!$E$3:$FH$3<=Statistik!$B$3)*('v38-41'!$D$5:$D$10=D6)*(('v38-41'!$E$5:$FH$10="telefon")+('v38-41'!$E$5:$FH$10="1/2 telefon")*.5))

Thanks again Fluff!

 

[Fluff]

You're welcome & thanks for the feedback.