Help with Combination of VLookUp and SumIfs

[massig]

Hi have 2 sheets

helpforsumif.xlsx

Microsoft Excel Workbook

1drv.ms

I am trying to find with the sumifs and sum all the number of touches that match the company name ... but i also want to add a second constraint - which is only sum if the touches are Before the date of the opportunity. I tried to put x lookup twice - but somehow it does not work...

I wonder if anyone can help refining the formula (if there is a formula that can actually do this at all!)

Or MInisheet 1

helpforsumif.xlsx
	A	B	C	D
1	COMPANY	OPPORTUNITY	CREATED DATE	NUMBER OF TOUCHES
2	ABC	Laptops	01/12/2022	no match
3	DEF	Servers	01/01/2023	8
4	GHI	Servers	01/03/2023	no match
5	SLM	Software	01/05/2023	no match
OPPS

Cell Formulas
Range	Formula
D2,D4:D5	D2	=XLOOKUP(A:A,'PIVOT FOR SUMIFS'!A:A,'PIVOT FOR SUMIFS'!C:C,"no match",FALSE)
D3	D3	=SUMIFS('PIVOT FOR SUMIFS'!C:C,'PIVOT FOR SUMIFS'!A:A,XLOOKUP(A:A,'PIVOT FOR SUMIFS'!A:A,'PIVOT FOR SUMIFS'!A:A,"no match",FALSE))

MINISHEET 2

helpforsumif.xlsx
	A	B	C
1	COMPANY	Touch DATE	TOUCHES
2	DEF	01/12/2022	2
3	DEF	23/12/2022	3
4	DEF	04/03/2023	3
PIVOT FOR SUMIFS

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Fluff]

How about

=SUMIFS('PIVOT FOR SUMIFS'!C:C,'PIVOT FOR SUMIFS'!A:A,A2,'PIVOT FOR SUMIFS'!B:B,"<="&C2)

 

[massig]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

HI - thank you - I have updated it - I am using Office 2021

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Help with Combination of VLookUp and SumIfs
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[massig]

How about

=SUMIFS('PIVOT FOR SUMIFS'!C:C,'PIVOT FOR SUMIFS'!A:A,A2,'PIVOT FOR SUMIFS'!B:B,"<="&C2)

I tried this formula and the result is 0 - instead of "5"

How about

=SUMIFS('PIVOT FOR SUMIFS'!C:C,'PIVOT FOR SUMIFS'!A:A,A2,'PIVOT FOR SUMIFS'!B:B,"<="&C2)

 

[massig]

I have also posted in Help with Combination of VLookUp and SumIfs

 

[Fluff]

I works for me

helpforsumif.xlsx
	A	B	C		E
1	COMPANY	OPPORTUNITY	CREATED DATE
2	ABC	Laptops	01/12/2022		0
3	DEF	Servers	01/01/2023		5
4	GHI	Servers	01/03/2023		0
5	SLM	Software	01/05/2023		0
OPPS

Cell Formulas
Range	Formula
E2:E5	E2	=SUMIFS('PIVOT FOR SUMIFS'!C:C,'PIVOT FOR SUMIFS'!A:A,A2,'PIVOT FOR SUMIFS'!B:B,"<="&C2)

 

[massig]

I used the formula =SUMIFS('PIVOT FOR SUMIFS'!C:C,'PIVOT FOR SUMIFS'!A:A,A3,'PIVOT FOR SUMIFS'!B:B,"<="&C3) and that worked Thank you!

 

[Fluff]

Glad to help & thanks for the feedback.