Summing data based on certain cells

I have a spreadsheet that has data for each month. At the bottom of each
month is a summary of the data in four categories. What I need to do is for
the data to be summed automatically, based on the four categories when I
insert it for month X. I am currently using

=SUMIF(INDEX($H:$H,N93):INDEX($H:$H,O93),N94,INDEX ($M:$M,N93):INDEX($M:$M,O93))+SUMIF(INDEX($H:$H,N9 3):INDEX($H:$H,O93),N94,INDEX($N:$N,N93):INDEX($N: $N,O93))

Whe
Column H is where the category definition is for each line item
N93 is the cell that tells the formula what the starting row is
O93 is the cell that tells the formula what the ending row is
N94 is the Category that is being summed in column H
Column M and N is where the numbers are that are being summed.

I have since migrated to Excel 2007 so the SUMIFS command is available now.
My question is - is there a more elegant way of doing this? Also, I currently
have to physically input the row number into N93 and O93. The problem is that
if I insert data into a prior month, it obviously changes all the start and
end rows for the months below and I have to fix them manually. Is there a way
to fix the start and end rows absolutely so that if I do insert data in a
prior month the reference in N93 and O93 will automatically update?

It sounds like you have another column containing dates? I'll assume it's
column A, and N93 and O93 now contain dates (month boundaries).

=SUMIFS($A:$A,"="&N93,$A:$A,"="&O93,$H:$H,N94,$M :$N)

Note: I don't have 2007, but I believe this is the correct syntax. Please
refer to the help file for guidance if my syntax is in error.

Alternative:
=SUMPRODUCT(--($A:$A=N93),--($A:$A=O93),--($H:$H=N94),($M:$N)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"MurrayBarn" wrote:

I have a spreadsheet that has data for each month. At the bottom of each
month is a summary of the data in four categories. What I need to do is for
the data to be summed automatically, based on the four categories when I
insert it for month X. I am currently using

=SUMIF(INDEX($H:$H,N93):INDEX($H:$H,O93),N94,INDEX ($M:$M,N93):INDEX($M:$M,O93))+SUMIF(INDEX($H:$H,N9 3):INDEX($H:$H,O93),N94,INDEX($N:$N,N93):INDEX($N: $N,O93))

Whe
Column H is where the category definition is for each line item
N93 is the cell that tells the formula what the starting row is
O93 is the cell that tells the formula what the ending row is
N94 is the Category that is being summed in column H
Column M and N is where the numbers are that are being summed.

I have since migrated to Excel 2007 so the SUMIFS command is available now.
My question is - is there a more elegant way of doing this? Also, I currently
have to physically input the row number into N93 and O93. The problem is that
if I insert data into a prior month, it obviously changes all the start and
end rows for the months below and I have to fix them manually. Is there a way
to fix the start and end rows absolutely so that if I do insert data in a
prior month the reference in N93 and O93 will automatically update?

Thanks Luke

Your first formula doesnt work and I havent worked out why yet. Also, I do
have a date field but I am not using it as I split the data into blocks per
month. However now that you mention it, it would be awesome if I could use
the date field as well so I dont have to stuff around sorting the date into
months. The date field would then be used to sum the below for all dates
falling in Month A, B, C etc

Regards
Murray

"Luke M" wrote:

It sounds like you have another column containing dates? I'll assume it's
column A, and N93 and O93 now contain dates (month boundaries).

=SUMIFS($A:$A,"="&N93,$A:$A,"="&O93,$H:$H,N94,$M :$N)

Note: I don't have 2007, but I believe this is the correct syntax. Please
refer to the help file for guidance if my syntax is in error.

Alternative:
=SUMPRODUCT(--($A:$A=N93),--($A:$A=O93),--($H:$H=N94),($M:$N)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"MurrayBarn" wrote:

I have a spreadsheet that has data for each month. At the bottom of each
month is a summary of the data in four categories. What I need to do is for
the data to be summed automatically, based on the four categories when I
insert it for month X. I am currently using

=SUMIF(INDEX($H:$H,N93):INDEX($H:$H,O93),N94,INDEX ($M:$M,N93):INDEX($M:$M,O93))+SUMIF(INDEX($H:$H,N9 3):INDEX($H:$H,O93),N94,INDEX($N:$N,N93):INDEX($N: $N,O93))

Whe
Column H is where the category definition is for each line item
N93 is the cell that tells the formula what the starting row is
O93 is the cell that tells the formula what the ending row is
N94 is the Category that is being summed in column H
Column M and N is where the numbers are that are being summed.

I have since migrated to Excel 2007 so the SUMIFS command is available now.
My question is - is there a more elegant way of doing this? Also, I currently
have to physically input the row number into N93 and O93. The problem is that
if I insert data into a prior month, it obviously changes all the start and
end rows for the months below and I have to fix them manually. Is there a way
to fix the start and end rows absolutely so that if I do insert data in a
prior month the reference in N93 and O93 will automatically update?

Hi,

Most likely you are not using Excel 2007, therefore the formula should be

=SUMPRODUCT(--($A:$A=N93),--($A:$A=O93),--($H:$H=N94),$M:$N)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"MurrayBarn" wrote:

Thanks Luke

Your first formula doesnt work and I havent worked out why yet. Also, I do
have a date field but I am not using it as I split the data into blocks per
month. However now that you mention it, it would be awesome if I could use
the date field as well so I dont have to stuff around sorting the date into
months. The date field would then be used to sum the below for all dates
falling in Month A, B, C etc

Regards
Murray

"Luke M" wrote:

It sounds like you have another column containing dates? I'll assume it's
column A, and N93 and O93 now contain dates (month boundaries).

=SUMIFS($A:$A,"="&N93,$A:$A,"="&O93,$H:$H,N94,$M :$N)

Note: I don't have 2007, but I believe this is the correct syntax. Please
refer to the help file for guidance if my syntax is in error.

Alternative:
=SUMPRODUCT(--($A:$A=N93),--($A:$A=O93),--($H:$H=N94),($M:$N)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"MurrayBarn" wrote:

I have a spreadsheet that has data for each month. At the bottom of each
month is a summary of the data in four categories. What I need to do is for
the data to be summed automatically, based on the four categories when I
insert it for month X. I am currently using

=SUMIF(INDEX($H:$H,N93):INDEX($H:$H,O93),N94,INDEX ($M:$M,N93):INDEX($M:$M,O93))+SUMIF(INDEX($H:$H,N9 3):INDEX($H:$H,O93),N94,INDEX($N:$N,N93):INDEX($N: $N,O93))

Whe
Column H is where the category definition is for each line item
N93 is the cell that tells the formula what the starting row is
O93 is the cell that tells the formula what the ending row is
N94 is the Category that is being summed in column H
Column M and N is where the numbers are that are being summed.

I have since migrated to Excel 2007 so the SUMIFS command is available now.
My question is - is there a more elegant way of doing this? Also, I currently
have to physically input the row number into N93 and O93. The problem is that
if I insert data into a prior month, it obviously changes all the start and
end rows for the months below and I have to fix them manually. Is there a way
to fix the start and end rows absolutely so that if I do insert data in a
prior month the reference in N93 and O93 will automatically update?

In XL07 you can use entire rows in Sumproduct formulas, but you really
shouldn't since it does use a lot of resources.

Say your date field is Column G, using *true* XL dates.
No sorting is necessary.

Enter start date in N93, and end date in O93.
Enter Category in N94.

Try this:

=SUMPRODUCT((G2:G90=N93)*(G2:G90=O93)*(H2:H90=N9 4)*M2:N90)
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"MurrayBarn" wrote in message
...
Thanks Luke

Your first formula doesnt work and I havent worked out why yet. Also, I do
have a date field but I am not using it as I split the data into blocks per
month. However now that you mention it, it would be awesome if I could use
the date field as well so I dont have to stuff around sorting the date into
months. The date field would then be used to sum the below for all dates
falling in Month A, B, C etc

Regards
Murray

"Luke M" wrote:

It sounds like you have another column containing dates? I'll assume it's
column A, and N93 and O93 now contain dates (month boundaries).

=SUMIFS($A:$A,"="&N93,$A:$A,"="&O93,$H:$H,N94,$M :$N)

Note: I don't have 2007, but I believe this is the correct syntax. Please
refer to the help file for guidance if my syntax is in error.

Alternative:
=SUMPRODUCT(--($A:$A=N93),--($A:$A=O93),--($H:$H=N94),($M:$N)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"MurrayBarn" wrote:

I have a spreadsheet that has data for each month. At the bottom of each
month is a summary of the data in four categories. What I need to do is
for
the data to be summed automatically, based on the four categories when I
insert it for month X. I am currently using

=SUMIF(INDEX($H:$H,N93):INDEX($H:$H,O93),N94,INDEX ($M:$M,N93):INDEX($M:$M,O93))+SUMIF(INDEX($H:$H,N9 3):INDEX($H:$H,O93),N94,INDEX($N:$N,N93):INDEX($N: $N,O93))

Whe
Column H is where the category definition is for each line item
N93 is the cell that tells the formula what the starting row is
O93 is the cell that tells the formula what the ending row is
N94 is the Category that is being summed in column H
Column M and N is where the numbers are that are being summed.

I have since migrated to Excel 2007 so the SUMIFS command is available
now.
My question is - is there a more elegant way of doing this? Also, I
currently
have to physically input the row number into N93 and O93. The problem is
that
if I insert data into a prior month, it obviously changes all the start
and
end rows for the months below and I have to fix them manually. Is there
a way
to fix the start and end rows absolutely so that if I do insert data in
a
prior month the reference in N93 and O93 will automatically update?