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.
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...
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.....
SergeiBaklan - 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?
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.