=SUMIF Formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1425591%22%20slang%3D%22en-US%22%3E%3DSUMIF%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1425591%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EHi%20All%2C%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EUsing%20Sum%20if%20as%20a%20tally%20to%20work%20out%20how%20much%20weld%20a%20welder%20has%20done%2C%20so%20i%20have%20multiple%20tables%20of%20weld%20id's%20then%20beside%20them%20the%20length%20they%20did.%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EWelder%20ID%20%3D%20Column%20D%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3ELength%20%3D%20Column%20C%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EBelow%20is%20the%20formula%20im%20using%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3DSUMIF(D5%3AD44%2C%22B.M.05%22%2CC5%3AC44)%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EHowever%20instead%20of%20just%20D5%3AD44%20i%20want%20to%20be%20able%20to%20do%20this%20to%20I5%3AI44%20aswell%20at%20the%20same%20time%20aswell%20as%20instead%20of%20just%20C5%3AC45%20i%20want%20H5%3A45%20aswell.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EWhat%20is%20the%20best%20way%20to%20go%20around%20this%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ECheers%2C%20Josh%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1425591%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1425982%22%20slang%3D%22en-US%22%3ERe%3A%20%3DSUMIF%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1425982%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F574846%22%20target%3D%22_blank%22%3E%40CBHJoshD%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJust%20the%20change%20reference%20column%20from%20D%20to%20I%20and%20C%20to%20H.%20Make%20sure%20the%20number%20of%20rows%20are%20same%20in%20both%20data%20range%20i.e.%20if%20range%20and%20sum%20range.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1425992%22%20slang%3D%22en-US%22%3ERe%3A%20%3DSUMIF%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1425992%22%20slang%3D%22en-US%22%3EI%20would%20like%20to%20do%20both%20the%20of%20the%20columns%20in%20the%20one%20formula%2C%20I%20need%20to%20get%20the%20result%20all%20in%20the%20one%20cell%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1425999%22%20slang%3D%22en-US%22%3ERe%3A%20%3DSUMIF%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1425999%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F574846%22%20target%3D%22_blank%22%3E%40CBHJoshD%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUse%20SUMIF%20twice.%20SUMIF(once%20set%20of%20condition)%20%2B%20SUMIF(second%20set%20of%20condition).%3C%2FP%3E%3CP%3EThis%20will%20give%20result%20in%20a%20single%20cell.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1426067%22%20slang%3D%22en-US%22%3ERe%3A%20%3DSUMIF%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1426067%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F574846%22%20target%3D%22_blank%22%3E%40CBHJoshD%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUMPRODUCT(--(IF(%7B1%2C0%7D%2CD5%3AD44%2CI5%3AI44)%3D%22B.M.05%22)*IF(%7B1%2C0%7D%2CC5%3AC44%2CH5%3AH44))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hi All,

 

Using Sum if as a tally to work out how much weld a welder has done, so i have multiple tables of weld id's then beside them the length they did.

 

Welder ID = Column D

Length = Column C

 

Below is the formula im using;

=SUMIF(D5:D44,"B.M.05",C5:C44)

 

However instead of just D5:D44 i want to be able to do this to I5:I44 aswell at the same time aswell as instead of just C5:C45 i want H5:45 aswell.

 

What is the best way to go around this?

 

Cheers, Josh

 

4 Replies
Highlighted

@CBHJoshD 

Just the change reference column from D to I and C to H. Make sure the number of rows are same in both data range i.e. if range and sum range.

Highlighted
I would like to do both the of the columns in the one formula, I need to get the result all in the one cell
Highlighted

@CBHJoshD 

Use SUMIF twice. SUMIF(once set of condition) + SUMIF(second set of condition).

This will give result in a single cell.

Highlighted

@CBHJoshD 

It could be

=SUMPRODUCT(--(IF({1,0},D5:D44,I5:I44)="B.M.05")*IF({1,0},C5:C44,H5:H44))