SOLVED
Home

Advanced Sumifs Help Needed

%3CLINGO-SUB%20id%3D%22lingo-sub-571346%22%20slang%3D%22en-US%22%3EAdvanced%20Sumifs%20Help%20Needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-571346%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20interested%20in%20how%20to%20(sumifs)%20between%20a%20range%20of%20equipment%20codes.%20For%20example%2C%20we%20have%204%20columns%20with%20about%2020%2C000%20rows%20(of%20line%20items)%20being%20all%20individual%20pieces%20of%20equipment.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EColumn%201%20-%20Invoice%20%23%3C%2FP%3E%3CP%3EColumn%202%20-%20Equipment%20code%20(ranging)%3C%2FP%3E%3CP%3EColumn%203%20-%20Vendor%20(yes%20or%20no)%3C%2FP%3E%3CP%3EColumn%204%20-%20Price%20for%20each%20line%20item%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20original%20formula%20would%20only%20account%20for%20individual%20equipment%20codes%2C%20for%20example%2C%20Code%2010000%20meaning%20lawnmower.%20Well%2C%20I%20am%20now%20put%20into%20the%20situation%20to%20sum%20if%20items%20are%20between%20a%20range%20of%20codes%20%26amp%3B%20here%20they%20are.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3ELawnmower%20(100000%20%E2%80%93%20399999)%3C%2FLI%3E%3CLI%3EBlowers%20(400000%20%E2%80%93%20499999)%3C%2FLI%3E%3CLI%3Eweed%20wacker%20(500000%20%E2%80%93%20599999)%3C%2FLI%3E%3CLI%3EEdgers%20(600000%20%E2%80%93%20899999)%3C%2FLI%3E%3CLI%3EMisc%20items%20(Misc-%23001)%3C%2FLI%3E%3C%2FUL%3E%3CP%3EAny%20ideas%3F%20I%20would%20like%20to%20sum%20totals%20if%20the%20invoice%20%23%20is%20matched%2C%20the%20vendor%20is%20(yes)%20and%20the%20equipment%20codes%20are%20within%20the%20range%20(100000%20%E2%80%93%20399999)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-571346%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-571386%22%20slang%3D%22en-US%22%3ERe%3A%20Advanced%20Sumifs%20Help%20Needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-571386%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F342710%22%20target%3D%22_blank%22%3E%40CasonTheOnly%3C%2FA%3E%26nbsp%3B%2C%20that's%20like%3C%2FP%3E%0A%3CPRE%3E%3DSUMIFS(Price%2CCode%2C%20%22%26gt%3B%3D%22%26amp%3B100000%2CCode%2C%22%26lt%3B%3D%22%26amp%3B399999%2CVendor%2C%22Yes%22)%3C%2FPRE%3E%0A%3CP%3EAs%20variant%20PivotTable%20could%20be%20more%20suitable%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-574787%22%20slang%3D%22en-US%22%3ERe%3A%20Advanced%20Sumifs%20Help%20Needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-574787%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BI%20may%20be%20structuring%20this%20wrong.%20I've%20attached%20a%20screenshot%20this%20time%20to%20give%20you%20an%20idea%20of%20what%20I'm%20working%20with%20(sorry%20for%20the%203mb%20file%20size....)%20Thanks%20for%20the%20response!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECason%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-574795%22%20slang%3D%22en-US%22%3ERe%3A%20Advanced%20Sumifs%20Help%20Needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-574795%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F342710%22%20target%3D%22_blank%22%3E%40CasonTheOnly%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHi%20Cason%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20the%20middle%20it%20shall%20be%3C%2FP%3E%0A%3CPRE%3E...%2CE319%3AE353%2C%22%26gt%3B%3D400000%22%2CE319%3AE353%2C%22%26lt%3B%3D499999%22%2C...%3C%2FPRE%3E%0A%3CP%3EI%20guess%20you%20have%20no%20name%20%22code%22%20defined.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-575360%22%20slang%3D%22en-US%22%3ERe%3A%20Advanced%20Sumifs%20Help%20Needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-575360%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BThe%20formula%20is%20now%20having%20issues%20summing%20the%20totals.%20It%20keeps%20calculating%20%240.%20Referring%20back%20to%20my%20screenshot%20(I've%20reattached%20for%20your%20convenience)%2C%20I%20previously%20found%20the%20issue%20was%20coming%20from%20the%20list%20of%20invoices%20I%20am%20trying%20to%20match%20(AE321%3A%20AE350)%2C%20almost%20as%20if%20it%20would%20not%20correctly%20match%20each%20individual%20invoice%20number.%20I%20was%20then%20forced%20to%20build%20another%20sheet%20that%20would%20sum%20the%20totals%20within%20each%20individual%20invoice%20(AE321)%2C%20(AE322)%2C%20etc.%20instead%20of%20being%20able%20to%20select%20all%20of%20them%20at%20once%20(AE321%3A%20AE350).%20However%2C%20even%20with%20your%20formula%20recommendation%20selecting%20on%20ONE%20invoice%20%23%20(AE321)%2C%20it%20still%20sums%20to%200.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-575403%22%20slang%3D%22en-US%22%3ERe%3A%20Advanced%20Sumifs%20Help%20Needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-575403%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F342710%22%20target%3D%22_blank%22%3E%40CasonTheOnly%3C%2FA%3E%26nbsp%3B%2C%20sorry%2C%20I%20missed%20you'd%20like%20to%20find%20sum%20for%20all%20invoices%2C%20not%20for%20concrete%20one.%20When%20warp%20your%20formula%20with%20SUMPRODUCT%20as%3C%2FP%3E%0A%3CPRE%3E%3DSUMPRODUCT(SUMIFS(...))%3C%2FPRE%3E%0A%3CP%3EOtherwise%20it%20takes%20first%20invoice%20from%20the%20list%20(203978)%20and%20you%20have%20no%20such%20in%20the%20range.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-575609%22%20slang%3D%22en-US%22%3ERe%3A%20Advanced%20Sumifs%20Help%20Needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-575609%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BThat%20formula%20is%20exactly%20what%20I%20needed.%20It%20wasn't%20working%20at%20first%20because%20I%20didn't%20convert%2Fformat%20my%20equipment%20codes%20to%20Numbers.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETHANK%20YOU!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-575620%22%20slang%3D%22en-US%22%3ERe%3A%20Advanced%20Sumifs%20Help%20Needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-575620%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F342710%22%20target%3D%22_blank%22%3E%40CasonTheOnly%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%2C%20glad%20to%20know%20you%20have%20one%20issue%20less%3C%2FP%3E%3C%2FLINGO-BODY%3E
CasonTheOnly
Occasional Contributor

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)

 

7 Replies

@CasonTheOnly , that's like

=SUMIFS(Price,Code, ">="&100000,Code,"<="&399999,Vendor,"Yes")

As variant PivotTable could be more suitable

 

@Sergei Baklan 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

@CasonTheOnly ,

 

Hi Cason,

 

In the middle it shall be

...,E319:E353,">=400000",E319:E353,"<=499999",...

I guess you have no name "code" defined.

@Sergei Baklan The formula is now having issues summing the totals. It keeps calculating $0. Referring back to my screenshot (I've reattached for your convenience), I previously found the issue was coming from the list of invoices I am trying to match (AE321: AE350), almost as if it would not correctly match each individual invoice number. I was then forced to build another sheet that would sum the totals within each individual invoice (AE321), (AE322), etc. instead of being able to select all of them at once (AE321: AE350). However, even with your formula recommendation selecting on ONE invoice # (AE321), it still sums to 0. 

 

Thanks!

Highlighted
Solution

@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.

@Sergei Baklan That formula is exactly what I needed. It wasn't working at first because I didn't convert/format my equipment codes to Numbers.

 

THANK YOU!

@CasonTheOnly , you are welcome, glad to know you have one issue less

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
20 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies