wSumifs/Index/Match

[chethead]

Hello,

I’m trying to write a formula that accomplishes the following:

Sum any quantity in named range EstQuantity that matches items in named range Raceway based on named range EstDescription and meets the criteria I set forth in EstSortCode

Raceway is a defined set of data – around 350 rows. This will never change.
Estxxx will change with every estimate I create. Named ranges were created for every column on the Estimate tab. I want to compare items in the EstDescription that match the items in Raceway and sum the quantity (EstQuantity)

Book1
	A	B	C	D
1	Sort Code	Description	Quantity	Total Hours
2	1000	3/4" CONDUIT - EMT	1378	10
3	2000	3/4" CONDUIT - EMT	1378	10
4	1000	1/2" CONN SS STL - EMT	92	2
5	1000	3/4" CONN SS STL - EMT	94	2
6	1000	1/2" COUPLING SS STL - EMT	90	2
7	1000	1 1/2" CONDUIT - EMT	20	0.5
8	1000	3/4" COUPLING SS STL - EMT	126	2.25
Sheet1

In the above table, I want to add the quantity of row 2 and row 7 because they meet the correct sort code and description. Row 3 meets all criteria except sort code, so I don't want that summed. Rows 4, 5, 6 and 8 description does not match anything in the named range Raceway, found on another sheet.

For total hours, I used this formula and it seems to be working great =SUM(SUMIFS(EstTotalHours,,EstSortCode,{1000,4000,5000,8000})). So now I am looking to pick out specific pieces of this formula based on EstDescription matching Raceway and summing corresponding quantity.

Thank you,
Rob

 

[jasonb75]

Not seeing how Estxxx relates to the rest of the detail in your post, is that referring to the sort codes?

Try this, array confirmed with Ctrl Shift Enter.

=SUM(SUMIFS(EstQuantity,EstSortCode,{1000,4000,5000,8000},EstDescription,Raceway))

 

[chethead]

Thank you Jason.

The only thing I was trying to imply is that any named ranged starting with Est will change every time I use the spreadsheet - maybe just too much info.

It seems like your solution works. I went through 2,000+ lines and tried highlighting all quantities that meet the criteria. I was off a little but I may have missed some. I will work with it a bit.

One other question - I want to subtract out certain items so I will use your formula and then subtract using a tweaked version of that formula. do I use shift-ctrl-enter at the end of the formula or do i put in curly brackets each for each portion of the formula?

{=SUM(SUMIFS(EstQuantity,EstSortCode,{1000,4000,5000,8000},EstDescription,Raceway))-SUM(SUMIFS(EstQuantity,EstSystem," || 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENT",{1000,4000,5000,8000},EstDescription,Raceway))}

or

{=SUM(SUMIFS(EstQuantity,EstSortCode,{1000,4000,5000,8000},EstDescription,Raceway))}-{SUM(SUMIFS(EstQuantity,EstSystem," || 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENT",{1000,4000,5000,8000},EstDescription,Raceway))}

Thank you for your help

 

[jasonb75]

do I use shift-ctrl-enter at the end of the formula or do i put in curly brackets each for each portion of the formula?

Just use CSE for the whole formula as with your first example above, if you type the the brackets then it will not work.

As you're increasing the complexity, one thing that I will add - you can only use arrays for 2 criteria in a single countifs function, 1 must be horizontal and the other vertical. With what you are currently using it works perfectly, the array constant is the horizontal array and the Raceway range is the vertical. If both of the arrays were in ranges then you would need to wrap one of them in the TRANSPOSE function.