Xlookup or Index match

[Tmcgrew05]

Hello, I am needing help with a formula and I just cannot get it quite right. I have tried Xlookup, Index Match, Vlookup and something is just not quite right. I have this first table on a separate worksheet within my wookbook. I am wanting to find the Sume_Earned_Value on a specific date (4/1/2022 and 4/2/2022) for each WorkCenter.

WorkCenter          ProdDate             Sume_Earned_Value

FBD 2	4/1/2022	501.2568
FEB B	4/1/2022	7523.8978
FFV 1	4/1/2022	1940.4482
FGB 1	4/1/2022	3353.3836
FGN	4/1/2022	1894.7309
FGP 1	4/1/2022	5722.4805
FGQ 1	4/1/2022	54.2294
FBD 2	4/2/2022	1104.039
FEB B	4/2/2022	4005.561
FFV 1	4/2/2022	843.282
FGB 1	4/2/2022	892.8009
FGN	4/2/2022	5588.6512
FGP 1	4/2/2022	1070.012

Work Center	SUPV	Desc	Date
			4/1/2022
FBD 2	BAY 1	985B/241B/881A/051A LENS-BEZEL ASSY
FEB B	BAY 1	010B CHL AFS/HG/SPORT, LH/RH	$
FFV 1	BAY 1	985B/241B Base HL	$
FGB 1	BAY 1	985B/241B REFL B SUBASY	$
FGN	BAY 1	Pes sub
FGP 1	BAY 1	U540 HL
FGQ 1	BAY 1	214 HL

Work Center	SUPV	Desc	Date
			4/2/2022
FBD 2	BAY 1	985B/241B/881A/051A LENS-BEZEL ASSY
FEB B	BAY 1	010B CHL AFS/HG/SPORT, LH/RH	$
FFV 1	BAY 1	985B/241B Base HL	$
FGB 1	BAY 1	985B/241B REFL B SUBASY	$
FGN	BAY 1	Pes sub
FGP 1	BAY 1	U540 HL
FGQ 1	BAY 1	214 HL

[#2]

You may use Sumproduct.
=Sumproduct(
(Table1[WorkCenter]="FBD 2")*(Table1[ProdDate]="4/1/2022")*
Table1[Sume_Earned_Value]
)
*Don't forget to lock the cell ref of "4/1/2022" in the formula with $ sign.
For e.g $D$2.

[#3]

Hi @Tmcgrew05 

You seem to run Excel 2021 or 365:

in I4:

=SUMIFS(D3:D15, B3:B15,F4:F10, C3:C15,I3)

Corresponding sample attached

[#4]

Hello @L z. 

Thank you very much. But how do I copy that down and then on to the next worksheet. So, my workbook contains 31 tabs, one for everyday, a summary tab and then my query tab with the data needed for each days earned value. How do I copy that formula down and then into each day (tab)? Could I use a VBA code to do it for me everyday since my query pulls the data everyday?

[#5]

I tried your formula but I could not get it to work. I guess I didn't understand where to lock in my cell ref of D2 because I do not see it in your example. I know a lil bit about excel but still learning formulas I have not used.

[#6]

Hi @Tmcgrew05 

how do I copy that down?

If you run Excel 2021 or 365 you don't have to copy down, the formula spills as a dynamic array

and then on to the next worksheet?

Without knowing your workbook difficult to say

[#7 · Accepted solution]

@Tmcgrew05 

See the attached sample workbook.