SOLVED

SUMIF for a range of one cell and a sum range of 242

%3CLINGO-SUB%20id%3D%22lingo-sub-1337201%22%20slang%3D%22en-US%22%3ESUMIF%20for%20a%20range%20of%20one%20cell%20and%20a%20sum%20range%20of%20242%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1337201%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20All%2C%20help%20greatly%20appreciated!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20approx%20300%20sections%20on%20one%20worksheet.%20Each%20section%20has%2040%20individual%20cells%2C%20each%20containing%20a%20value%20between%201%20to%2012%20(denoting%20months%20of%20the%20year).%20Alongside%20each%20of%20these%2040%20cells%20in%20each%20section%2C%20there%20are%20242%20columns%20with%20values%20in%20them.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20am%20trying%20to%20do%20is%20SUM%20the%20values%20in%20these%20242%20columns%20in%20the%20same%20row%20opposite%20all%20the%2040%20cells%20in%20each%20section%20containing%20the%20value%26nbsp%3B1%20to%2012%20(denoting%20months%20of%20the%20year).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20entered%20the%20formula%20%3DSUMIF(C8%2C1%2CD8%3AIK8)%20and%20the%20sum%20returned%20is%20%220%22%2C%20despite%20there%20being%20values%20in%20the%20cells%20D8%3AIK8.%20I%20have%20also%20tried%20%3DSUMIF(C8%2C%221%22%2CD8%3AIK8)%20and%20%3DSUMIF(C8%2C%20%221%22%2C%20D8%3AIY8)%20I%20think%20the%20issue%20maybe%20that%20the%20%22sum%20range%22%20needs%20to%20be%20the%20same%20as%20the%20%22range%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20question%20is%2C%20what%20formula%20do%20I%20need%20to%20enter%20if%20the%20%22sum%20range%22%20is%20much%20larger%20than%20the%20%22range%22%20(in%20my%20case%20244%20cells%20vs%201%20cell).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAll%20thoughts%20hugely%20appreciated!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20to%20all.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMatt%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1337201%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-1337218%22%20slang%3D%22en-US%22%3ERe%3A%20SUMIF%20for%20a%20range%20of%20one%20cell%20and%20a%20sum%20range%20of%20242%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1337218%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F636580%22%20target%3D%22_blank%22%3E%40Matt_7%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20general%20criteria%20range%20and%20sum%20range%20are%20to%20be%20the%20same%20size%2C%20if%20not%20Excel%20tries%20to%20adjust%20them.%20If%20you%20use%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DSUMIF(C8%2C1%2CD8%3AIK8)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Ethat%20means%20criteria%20range%20is%20from%20only%20one%20cell%2C%20and%20actually%20calculations%20will%20be%20done%20on%20only%20one%20cell%20of%20the%20sum%20range%2C%20i.e.%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DSUMIF(C8%2C1%2CD8)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EFinally%2C%20that%20formula%20is%20equivalent%20of%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIF(C8%3D1%2CD8%2C0)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EFrom%20your%20sample%20I%20didn't%20catch%20what%20actually%20you%20try%20to%20calculate.%20It%20only%20states%20that%20formula%20doesn't%20work.%20It%20works%2C%20but%20not%20as%20you%20expect.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1337225%22%20slang%3D%22en-US%22%3ERe%3A%20SUMIF%20for%20a%20range%20of%20one%20cell%20and%20a%20sum%20range%20of%20242%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1337225%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F636580%22%20target%3D%22_blank%22%3E%40Matt_7%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20you%20are%20considering%20the%20wrong%20formula%20here...what%20I%20believe%20you%20need%20is%20%3A%23%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(C8%3D1%2CSUM(%3CSPAN%3ED8%3AIK8)%2CFALSE)%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1337289%22%20slang%3D%22en-US%22%3ERe%3A%20SUMIF%20for%20a%20range%20of%20one%20cell%20and%20a%20sum%20range%20of%20242%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1337289%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%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESergei%20-%20many%20thanks%20for%20your%20kind%20reply.%20Just%20to%20clarify%2C%20there%20are%20many%20thousands%20of%20cells%20in%20the%20worksheet%20that%20have%20a%20value%20entered%20in%20to%20the%20cell%20reserved%20for%20months%20(1%20for%20Jan%20to%2012%20for%20Dec)%20and%20alongside%20each%20one%20of%20these%20%22month%22%20cells%2C%20there%20are%20242%20cells%20in%20the%20same%20row%2C%20with%20values%20in%20them.%20I%20then%20have%2012%20columns%20to%20the%20far%20RHS%20of%20the%20worksheet%20that%20totalisers%20for%20each%20month.%20Every%20time%20a%20%221%22%20appears%20in%20one%20of%20the%20month%20cells%2C%20I%20need%20to%20add%20up%20the%20values%20in%20the%20242%20cells%20in%20the%20same%20row%20to%20the%20immediate%20right%20of%20the%20month%20cell%20and%20that%20total%20value%20will%20then%20appear%20in%20the%20January%20totaliser%20column%20to%20the%20far%20right.%20Hope%20this%20clarifies........all%20feedback%20welcome!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1337291%22%20slang%3D%22en-US%22%3ERe%3A%20SUMIF%20for%20a%20range%20of%20one%20cell%20and%20a%20sum%20range%20of%20242%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1337291%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F531239%22%20target%3D%22_blank%22%3E%40Charla74%3C%2FA%3E%26nbsp%3B%26nbsp%3B-%20many%20thanks%20for%20your%20kind%20reply.%20Just%20to%20clarify%2C%20there%20are%20many%20thousands%20of%20cells%20in%20the%20worksheet%20that%20have%20a%20value%20entered%20in%20to%20the%20cell%20reserved%20for%20months%20(1%20for%20Jan%20to%2012%20for%20Dec)%20and%20alongside%20each%20one%20of%20these%20%22month%22%20cells%2C%20there%20are%20242%20cells%20in%20the%20same%20row%2C%20with%20values%20in%20them.%20I%20then%20have%2012%20columns%20to%20the%20far%20RHS%20of%20the%20worksheet%20that%20totalisers%20for%20each%20month.%20Every%20time%20a%20%221%22%20appears%20in%20one%20of%20the%20month%20cells%2C%20I%20need%20to%20add%20up%20the%20values%20in%20the%20242%20cells%20in%20the%20same%20row%20to%20the%20immediate%20right%20of%20the%20month%20cell%20and%20that%20total%20value%20will%20then%20appear%20in%20the%20January%20totaliser%20column%20to%20the%20far%20right.%20Hope%20this%20clarifies........all%20feedback%20welcome!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1337300%22%20slang%3D%22en-US%22%3ERe%3A%20SUMIF%20for%20a%20range%20of%20one%20cell%20and%20a%20sum%20range%20of%20242%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1337300%22%20slang%3D%22en-US%22%3EJust%20to%20further%20clarify%2C%20if%20I%20trace%20precedents%20having%20entered%20the%20formula%2C%20all%20the%20correct%20cells%20are%20%22boxed%22%20-%20just%20the%20SUM%20is%20wholly%20wrong.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1337406%22%20slang%3D%22en-US%22%3ERe%3A%20SUMIF%20for%20a%20range%20of%20one%20cell%20and%20a%20sum%20range%20of%20242%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1337406%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F531239%22%20target%3D%22_blank%22%3E%40Charla74%3C%2FA%3E%26nbsp%3B-%20many%20thanks%20again%20for%20engaging%20on%20this.%20We%20are%20still%20not%20quite%20there%20and%20that%20is%20my%20fault%2C%20for%20my%20poor%20explanation.%20The%20rows%20are%20totalising%20individual%20pieces%20of%20equipment%20and%20there%20are%20two%20rows%20for%20each%20piece%20of%20equipment.%20The%20name%20of%20the%20equipment%20is%20off%20to%20the%20left%20of%20the%20worksheet%20that%20I%20sent%20to%20you.%20The%20worksheet%20permits%20each%20piece%20of%20equipment%20to%20be%20moved%20up%20to%2040%20times%20between%20inventory%20checks.%20Inventory%20checks%20are%20annual.%20The%20idea%20is%20that%20this%20worksheet%20tracks%20the%20movement%20of%20equipment%20out%20of%20the%20company's%20stores%20and%20trucks%20to%20customers.%20So%20for%20each%20piece%20of%20equipment%2C%20I%20am%20trying%20to%20totalise%20the%20net%20in%20and%20out%20movements%20every%20month.%20Hence%2012%20columns%20to%20the%20far%20right.%20The%20column%20for%20January%20(tagged%20to%20the%20fig%201)%20will%20pick%20up%20the%20net%20movements%20in%20or%20out%20(might%20be%206%20out%20and%209%20in%2C%20leading%20to%20a%20figure%20of%20%2B3).%20The%20column%20for%20Feb%20(tagged%20to%20the%20fig%202%20)%20will%20pick%20up%26nbsp%3Bthe%20net%20movements%20in%20or%20out%20(might%20be%2010%20out%20and%208%20in%2C%20leading%20to%20a%20figure%20of%20-2)......and%20so%20on.....%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1337371%22%20slang%3D%22en-US%22%3ERe%3A%20SUMIF%20for%20a%20range%20of%20one%20cell%20and%20a%20sum%20range%20of%20242%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1337371%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F636580%22%20target%3D%22_blank%22%3E%40Matt_7%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMaybe%20i'm%20still%20not%20aligned%20with%20the%20result%20you're%20looking%20for%20but%20see%20attached%20my%20updated%20version%20of%20your%20sheet%20and%20an%20additional%20suggestion%20sheet%20which%20may%20(or%20may%20not)%20be%20of%20use.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20problem%20I%20see%20with%20your%20initial%20formula%20is%20that%20you%20are%20only%20asking%20for%20one%20condition%20(1)%20where%20there%20are%20a%20possible%2012%20conditions...%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Dear All, help greatly appreciated!

 

I have approx 300 sections on one worksheet. Each section has 40 individual cells, each containing a value between 1 to 12 (denoting months of the year). Alongside each of these 40 cells in each section, there are 242 columns with values in them.

 

What I am trying to do is SUM the values in these 242 columns in the same row opposite all the 40 cells in each section containing the value 1 to 12 (denoting months of the year).

 

I have entered the formula =SUMIF(C8,1,D8:IK8) and the sum returned is "0", despite there being values in the cells D8:IK8. I have also tried =SUMIF(C8,"1",D8:IK8) and =SUMIF(C8, "1", D8:IY8) I think the issue maybe that the "sum range" needs to be the same as the "range".

 

My question is, what formula do I need to enter if the "sum range" is much larger than the "range" (in my case 244 cells vs 1 cell).

 

All thoughts hugely appreciated!

 

Best to all.

 

Matt

21 Replies
Highlighted

@Matt_7 

In general criteria range and sum range are to be the same size, if not Excel tries to adjust them. If you use

=SUMIF(C8,1,D8:IK8)

that means criteria range is from only one cell, and actually calculations will be done on only one cell of the sum range, i.e.

=SUMIF(C8,1,D8)

Finally, that formula is equivalent of

=IF(C8=1,D8,0)

From your sample I didn't catch what actually you try to calculate. It only states that formula doesn't work. It works, but not as you expect.

 

Highlighted

@Matt_7 

 

I think you are considering the wrong formula here...what I believe you need is :#

 

=IF(C8=1,SUM(D8:IK8),FALSE)

Highlighted

@Sergei Baklan 

 

Sergei - many thanks for your kind reply. Just to clarify, there are many thousands of cells in the worksheet that have a value entered in to the cell reserved for months (1 for Jan to 12 for Dec) and alongside each one of these "month" cells, there are 242 cells in the same row, with values in them. I then have 12 columns to the far RHS of the worksheet that totalisers for each month. Every time a "1" appears in one of the month cells, I need to add up the values in the 242 cells in the same row to the immediate right of the month cell and that total value will then appear in the January totaliser column to the far right. Hope this clarifies........all feedback welcome!

Highlighted

@Charla74  - many thanks for your kind reply. Just to clarify, there are many thousands of cells in the worksheet that have a value entered in to the cell reserved for months (1 for Jan to 12 for Dec) and alongside each one of these "month" cells, there are 242 cells in the same row, with values in them. I then have 12 columns to the far RHS of the worksheet that totalisers for each month. Every time a "1" appears in one of the month cells, I need to add up the values in the 242 cells in the same row to the immediate right of the month cell and that total value will then appear in the January totaliser column to the far right. Hope this clarifies........all feedback welcome!

Highlighted
Just to further clarify, if I trace precedents having entered the formula, all the correct cells are "boxed" - just the SUM is wholly wrong.
Highlighted

@Matt_7 

 

Maybe i'm still not aligned with the result you're looking for but see attached my updated version of your sheet and an additional suggestion sheet which may (or may not) be of use.

 

The problem I see with your initial formula is that you are only asking for one condition (1) where there are a possible 12 conditions...

Highlighted

@Charla74 - many thanks again for engaging on this. We are still not quite there and that is my fault, for my poor explanation. The rows are totalising individual pieces of equipment and there are two rows for each piece of equipment. The name of the equipment is off to the left of the worksheet that I sent to you. The worksheet permits each piece of equipment to be moved up to 40 times between inventory checks. Inventory checks are annual. The idea is that this worksheet tracks the movement of equipment out of the company's stores and trucks to customers. So for each piece of equipment, I am trying to totalise the net in and out movements every month. Hence 12 columns to the far right. The column for January (tagged to the fig 1) will pick up the net movements in or out (might be 6 out and 9 in, leading to a figure of +3). The column for Feb (tagged to the fig 2 ) will pick up the net movements in or out (might be 10 out and 8 in, leading to a figure of -2)......and so on.....

Highlighted

@Matt_7 

Since it's not clear what are "monthly" cells, etc, let me re-word to be more close to your sample file

image.png

for the each of the months in defined in M1:X1 (Jan to Dec) we would like to calculate sum of values in each row starting from #5 and in columns from D to K. We return sum if value column C of the row is equal to month number, or zero otherwise. That's what @Charla74 suggested, but looks like not a right solution.

To map your business logic with which we are not familiar perhaps you may fill first couple of rows in above sample with data and manually added desired result in the right column. Please don't use etc. and other words instead of data - just headers and data as they are shall be for such small model.

Highlighted

@Matt_7 , @Charla74 

If without helper columns

=SUMPRODUCT($D$5:$K$7*($C$5:$C$7=M$2))

 

Highlighted

@Sergei Baklan - apologies for my slow response. I now attach a workbook that shows a real sample of one item, copied from the original workbook. I hope that this makes it clearer. I have placed the formulas in columns GGP to GGS. The trace precedents function, seems to show that I have entered the formulas correctly, but the result is clearly wrong. Looks to me as though as you have suggested the result range is following the criteria range (i.e. one cell only). 

 

I hate being beaten by these things....maybe we are in to VBA territory here? 

Highlighted
Best Response confirmed by Matt_7 (Occasional Contributor)
Solution

@Matt_7 

Perhaps formula in GGP6 could be

=IF($M6=GGP$2,SUM($N6:$IU6),0)+IF($IY6=GGP$2,SUM($IZ6:$SG6),0)

and copy it to the right and next rows. Other ranges could be added same way.

Highlighted

@Sergei Baklan - You are a legend my friend! 100% works. Thank you very much for your patience and your focus on this. Very much appreciated. I wish you and your family safe passage through this pandemic. Very best.

Highlighted

@Matt_7 , thank you and take care, be safe.

Highlighted

@Sergei Baklan - back again my friend for one more piece of advice if I may. Trying to SUM (in cell GHD10) a range of cells in the same row FXL10:GGO10 if cell FXG10 = 1 and if the corresponding criteria range of cells in Row 1 (FXL1:GGO1) = "E.D.F Sold". Seems simple enough to me, but the following formula returns the dreaded #VALUE! and not the value it should. I've gone through the syntax on the formula multiple times and cannot seem to find an issue. Is there something simple I am missing? I am entering the following formula =SUMIFS(FXL10:GGO10,FXG10,"1",FXL1:GGO1,"E.D.F Sold"). All help hugely welcome! Best

Highlighted

@Matt_7 

In your formula sum range and first criteria range are of different size (the latest just one cell), thus formula returns an error. If remove it as

=SUMIFS(FXL10:GGO10,FXL1:GGO1,"E.D.F Sold")

formula returns some result. However, I guess you'd like receive this result if only FXG10 is equal to 1, when

= IF(FXG10=1,SUMIFS(FXL10:GGO10,FXL1:GGO1,"E.D.F Sold"),0)
Highlighted

@Sergei Baklan - OK I fully get it now. As you say sum range and criteria range need to be congruent if using SUMIF or SUMIFS. If not congruent then need to use IF at the outset and then SUMIF or SUMIFS for the congruent part of the equation. A huge thanks again my friend. I really am very grateful for yr help!! Best

Highlighted

@Matt_7 

Matt - yes, exactly. Glad to help.

Highlighted

@Sergei Baklan - Sergei not sure whether you are still out there and hope all well? Picking up from where I left off on this project when we were last talking in April. You kindly helped me with the following formula =IF($Q8=GHS$3,SUMIFS($R8:$IY8,$R$1:$IY$1,"*"),0). Now starting to use this piece of the worksheet, I notice that this is not quite doing what I want it to do. What I want it to do is to SUM the values only in cells where the first row of the column has the "*" in it. The above formula is not achieving this. If there is a "*" in any of the range ($R$1:$IY$1), then all values in all cells in range $R8:$IY8 are being SUM'd, rather than just the cells where the columns are "headed" with "*". Is there a small tweak required to right this formula? Many thanks Sergei!

Highlighted

@Matt_81120 

Hi Matt,

 

Asterisk means any text. To use it as a character you shall use it with tilde "~*"

=IF($Q8=GHS$3,SUMIFS($R8:$IY8,$R$1:$IY$1,"~*"),0)

Above if the cell value is asterisk. If it, for example, within the text, when like "*~**"

Please check Sum if cells contain an asterisk for more details.