Forum Discussion
SUMIF for a range of one cell and a sum range of 242
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
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.
22 Replies
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!
- Just to further clarify, if I trace precedents having entered the formula, all the correct cells are "boxed" - just the SUM is wholly wrong.
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.
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!
Since it's not clear what are "monthly" cells, etc, let me re-word to be more close to your sample file
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.
