Embedded Vlookups in IFS formula and subtotaling?

[Dinger045]

I have attached an example I what I am looking for. The yellow highlights fields is what we want. And that is a subtotal of the claims in the Data Set 2 tab.


2 Sets of data, and from those two things we have to match up on 2 fields. Location, then cost center.

So If location # is 4, and cost center is 100100, the yellow cell for that in total claims should have a quantity of 3. Because there are 3 claims on data set 2 tab, that meet the criteria of location 4 and cost center 100100. And then in the next yellow for ext claims i would want it to read 2, because there are 2 yes' on data set 2 tab.


I am having trouble with the subtotaling into the vlookup to be able to embed that into an IFS formula.

I think an option would be tabling the second data set tab, but im not familiar with tables to vlookups that would then be embedded.

[TMS]

I think you should be looking at SUMIFS, or using a Pivot Table, rather than using IF and VLOOKUP.

[Dinger045]

Not familiar at all with SUMIFs, and I don't know how to get the Pivot table to display sums of the both counts in a layout form to make it total the criteria?

[Ace_XL]

In D2
=COUNTIFS('Data Set 2'!A:A,A2,'Data Set 2'!C:C,B2)

In E2
=COUNTIFS('Data Set 2'!A:A,A2,'Data Set 2'!C:C,B2,'Data Set 2'!D:D,"Yes")


Copy down