|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
August 27th, 2008, 03:11 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Changing conditional sum format to "Sum If"
I am currently using
=SUM(IF(Data!$E$2:$E$1700=2467,Data!$Q$2:$Q$1700,0 )) But I would instead like to reference cell B2 of the current worksheet rather than typing 2467. The reason being, I'd like to be able to drag this calculation down, and have the formula in the cell just below reference cell B3 of the current worksheet, etc. Are there any sumproduct experts available who could steer me in the right direction? Gina 
|
|
#2
August 27th, 2008, 03:18 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Changing conditional sum format to "Sum If"
I think you mean that you want to do this:
=SUMPRODUCT(--(Data!$E$2:$E$1700=$B$2),--(Data!$Q$2:$Q$1700)) No need for CSE when you enter it. Hope this helps. Pete On Aug 27, 3:11*pm, Gina wrote: I am currently using =SUM(IF(Data!$E$2:$E$1700=2467,Data!$Q$2:$Q$1700,0 )) But I would instead like to reference cell B2 of the current worksheet rather than typing 2467. *The reason being, I'd like to be able to drag this calculation down, and have the formula in the cell just below reference cell B3 of the current worksheet, etc. Are there any sumproduct experts available who could steer me in the right direction? Gina
|
|
#3
August 27th, 2008, 03:27 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Changing conditional sum format to "Sum If"
"Pete_UK" wrote: I think you mean that you want to do this: =SUMPRODUCT(--(Data!$E$2:$E$1700=$B$2),--(Data!$Q$2:$Q$1700)) No need for CSE when you enter it. Hope this helps. Pete On Aug 27, 3:11 pm, Gina wrote: I am currently using =SUM(IF(Data!$E$2:$E$1700=2467,Data!$Q$2:$Q$1700,0 )) But I would instead like to reference cell B2 of the current worksheet rather than typing 2467. The reason being, I'd like to be able to drag this calculation down, and have the formula in the cell just below reference cell B3 of the current worksheet, etc. Are there any sumproduct experts available who could steer me in the right direction? Gina This is exactly what I meant. It returned the same value and worked great. Thank you. Gina
|
|
#4
August 27th, 2008, 04:09 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Changing conditional sum format to "Sum If"
You're welcome, Gina - thanks for feeding back.
Pete On Aug 27, 3:27*pm, Gina wrote: "Pete_UK" wrote: I think you mean that you want to do this: =SUMPRODUCT(--(Data!$E$2:$E$1700=$B$2),--(Data!$Q$2:$Q$1700)) No need for CSE when you enter it. Hope this helps. Pete On Aug 27, 3:11 pm, Gina wrote: I am currently using =SUM(IF(Data!$E$2:$E$1700=2467,Data!$Q$2:$Q$1700,0 )) But I would instead like to reference cell B2 of the current worksheet rather than typing 2467. *The reason being, I'd like to be able to drag this calculation down, and have the formula in the cell just below reference cell B3 of the current worksheet, etc. Are there any sumproduct experts available who could steer me in the right direction? Gina This is exactly what I meant. *It returned the same value and worked great. * Thank you. Gina- Hide quoted text - - Show quoted text -
|
|
#5
August 27th, 2008, 05:27 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Changing conditional sum format to "Sum If"
My reading of the OP's question was that she wanted the B2 to change to B3
when copied downwards, in which case I would have thought not =SUMPRODUCT(--(Data!$E$2:$E$1700=$B$2),--(Data!$Q$2:$Q$1700)) but =SUMPRODUCT(--(Data!$E$2:$E$1700=$B2),--(Data!$Q$2:$Q$1700)) -- David Biddulph "Pete_UK" wrote in message ... I think you mean that you want to do this: =SUMPRODUCT(--(Data!$E$2:$E$1700=$B$2),--(Data!$Q$2:$Q$1700)) No need for CSE when you enter it. Hope this helps. Pete On Aug 27, 3:11 pm, Gina wrote: I am currently using =SUM(IF(Data!$E$2:$E$1700=2467,Data!$Q$2:$Q$1700,0 )) But I would instead like to reference cell B2 of the current worksheet rather than typing 2467. The reason being, I'd like to be able to drag this calculation down, and have the formula in the cell just below reference cell B3 of the current worksheet, etc.
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
