Summing all instances of a VLOOKUP Formula

[EMCNEIL]

It seems so easy but stumped!

Here is the formula:

=IFERROR(IF(F14="Basic", VLOOKUP(E14,'DAI Obligations'!$K$1:$AV$367,30, FALSE), 0),0)

If Cell F14 is "Basic", it picks up the Requisition Number in Cell E14 and finds a match in the DAI Obligation worksheet and brings back the number in column 30, which are the $$.

It's fine until there are multiple instances of the exact same Requisition Number, at which point it only picks up one of them then stops.

Looking for a way to SUM all of the instances, otherwise our data is no good. I have been thus far very unsuccessful.

I'd be super grateful for any help. Optimally I would just like to adjust the formula.

 

[AhoyNC]

Try:
Change ranges to match your data.

Book1
	E	F	G
13			Sum
14	12	Basic	59
Sheet1

Cell Formulas
Range	Formula
G14	G14	=IF($F$14="Basic",SUMPRODUCT(--('DAI Obligation '!$A$2:$A$10=Sheet1!$E$14),'DAI Obligation '!$AD$2:$AD$10),0)

DAI Obligation

Book1
	A	B																											AC	AD
1	REC#																													your col 30
2	1																													30
3	22																													20
4	12																													12
5	25																													15
6	12																													16
7	16																													23
8	16																													1
9	12																													25
10	12																													6
DAI Obligation

 

[Fluff]

Hi & welcome to MrExcel.
Have a look at the Sumif function

SUMIF Function

The Excel SUMIF function returns the sum of cells that meet a single condition. Criteria can be applied to dates, numbers, and text. The SUMIF function supports logical operators (>,

exceljet.net

 

[Yongle]

Perhaps
=SUMIF('Dai Obligations'!$K:$K,E14,'Dai Obligations'!$AN:$AN)


Edit: Ah @Fluff was faster to the draw

 

[EMCNEIL]

Try:
Change ranges to match your data.

Book1
	E	F	G
13			Sum
14	12	Basic	59
Sheet1

Cell Formulas
Range	Formula
G14	G14	=IF($F$14="Basic",SUMPRODUCT(--('DAI Obligation '!$A$2:$A$10=Sheet1!$E$14),'DAI Obligation '!$AD$2:$AD$10),0)

DAI Obligation

Book1
	A	B																											AC	AD
1	REC#																													your col 30
2	1																													30
3	22																													20
4	12																													12
5	25																													15
6	12																													16
7	16																													23
8	16																													1
9	12																													25
10	12																													6
DAI Obligation

Perhaps
=SUMIF('Dai Obligations'!$K:$K,E14,'Dai Obligations'!$AN:$AN)


Edit: Ah @Fluff was faster to the draw

Thanks, but that leaves out the other factors:

Sup Matls worksheet: The calculation is in the Sup Matls Worksheet. The requisition number (column E) is on the worksheet, as well as the criteria "Basic." (column F)

The criteria "Basic" needs to be met first. The drop down menu has other options and none of them are to be included in the calculation.

Then the requisition Number gets picked up (each row in column E in Sup Matl) Then it looks for the matching requisition # (column K on the 'DAI Obligations' worksheet) . The $$ needing to be summed up are in column AD or 30 of the DAI Obligations Worksheet.

 

[EMCNEIL]

Try:
Change ranges to match your data.

Book1
	E	F	G
13			Sum
14	12	Basic	59
Sheet1

Cell Formulas
Range	Formula
G14	G14	=IF($F$14="Basic",SUMPRODUCT(--('DAI Obligation '!$A$2:$A$10=Sheet1!$E$14),'DAI Obligation '!$AD$2:$AD$10),0)

DAI Obligation

Book1
	A	B																											AC	AD
1	REC#																													your col 30
2	1																													30
3	22																													20
4	12																													12
5	25																													15
6	12																													16
7	16																													23
8	16																													1
9	12																													25
10	12																													6
DAI Obligation

It looked like it would work: =IF($F9="Basic",SUMPRODUCT(--('DAI Obligations'!$K$2:$K$200='Sup Matl'$E14),'DAI Obligation '!$AD$2:$AD$200),0) but it's only recognizing it as text! Tried the control/shift/return and got nothing. Sup Matls worksheet: The calculation is in the Sup Matls Worksheet. The requisition number (column E) is on the worksheet, as well as the criteria "Basic." (column F) The criteria "Basic" needs to be met first. The drop down menu has other options and none of them are to be included in the calculation. Then the requisition Number gets picked up (each row in column E in Sup Matl) Then it looks for the matching requisition # (column K on the 'DAI Obligations' worksheet) . The $$ needing to be summed up are in column AD or 30 of the DAI Obligations Worksheet. ?? So frustrating.

 

[Fluff]

How about
=IFERROR(IF(F14="Basic",SUMIF('DAI Obligations'!$K$1:$K$367,E14,'DAI Obligations'!$AD$1:$AD$367),0),0)

However you vlookup is returning values from col AN, not col AD

 

[EMCNEIL]

How about
=IFERROR(IF(F14="Basic",SUMIF('DAI Obligations'!$K$1:$K$367,E14,'DAI Obligations'!$AD$1:$AD$367),0),0)

However you vlookup is returning values from col AN, not col AD

You are absolutely right, fixed all of the reference points, IT WORKS!!!!! Thank you Thank you Thank you!!!!!??

 

[Fluff]

Glad we could help & thanks for the feedback