Forum Discussion
Advanced Sumifs Help Needed
I am interested in how to (sumifs) between a range of equipment codes. For example, we have 4 columns with about 20,000 rows (of line items) being all individual pieces of equipment.
Column 1 - Invoice #
Column 2 - Equipment code (ranging)
Column 3 - Vendor (yes or no)
Column 4 - Price for each line item
My original formula would only account for individual equipment codes, for example, Code 10000 meaning lawnmower. Well, I am now put into the situation to sum if items are between a range of codes & here they are.
- Lawnmower (100000 – 399999)
- Blowers (400000 – 499999)
- weed wacker (500000 – 599999)
- Edgers (600000 – 899999)
- Misc items (Misc-#001)
Any ideas? I would like to sum totals if the invoice # is matched, the vendor is (yes) and the equipment codes are within the range (100000 – 399999)
CasonTheOnly , sorry, I missed you'd like to find sum for all invoices, not for concrete one. When warp your formula with SUMPRODUCT as
=SUMPRODUCT(SUMIFS(...))
Otherwise it takes first invoice from the list (203978) and you have no such in the range.
7 Replies
CasonTheOnly , that's like
=SUMIFS(Price,Code, ">="&100000,Code,"<="&399999,Vendor,"Yes")
As variant PivotTable could be more suitable
SergeiBaklan I may be structuring this wrong. I've attached a screenshot this time to give you an idea of what I'm working with (sorry for the 3mb file size....) Thanks for the response!
Cason
Hi Cason,
In the middle it shall be
...,E319:E353,">=400000",E319:E353,"<=499999",...
I guess you have no name "code" defined.
