Forum Discussion
SUMIF for a range of one cell and a sum range of 242
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.
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.
