sum/group by 2 conditions
 

A twist on sumif?
I'm trying to sum column B by 2 conditions (columns A and C) and display the
summation and the 2 conditions that match. The example below is very short,
but there are approx. 100 conditions for column A and C that form many
comibinations. I need to display the conditions (that both need to be met)
with the summation.

For example (the data is much larger than this):
A B C
13-0506 Striping Contract 10.00 610
13-0506 Striping Contract 5.00 480
13-0506 Striping Contract 4.00 480
13-0501 Maint Job Inspection 2.00 610
13-0506 Striping Contract 2.00 610

Desired result:

A B C
13-0506 Striping Contract 12.00 610
13-0506 Striping Contract 9.00 480
13-0501 Maint Job Inspection 2.00 610

sum/group by 2 conditions
 

Hi

try to use Sumproduct

=sumproduct(--($a$2:$a$1000=a1010)*($C$2:$c$1000=c1010);(b2:b100 0))

assuming that your data is on a2:c1000 and you will start the summary on
a1010.

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"novice" escreveu:

A twist on sumif?
I'm trying to sum column B by 2 conditions (columns A and C) and display the
summation and the 2 conditions that match. The example below is very short,
but there are approx. 100 conditions for column A and C that form many
comibinations. I need to display the conditions (that both need to be met)
with the summation.

For example (the data is much larger than this):
A B C
13-0506 Striping Contract 10.00 610
13-0506 Striping Contract 5.00 480
13-0506 Striping Contract 4.00 480
13-0501 Maint Job Inspection 2.00 610
13-0506 Striping Contract 2.00 610

Desired result:

A B C
13-0506 Striping Contract 12.00 610
13-0506 Striping Contract 9.00 480
13-0501 Maint Job Inspection 2.00 610

sum/group by 2 conditions
 

Sorry big guy that didn't work. Maybe I wasn't clear enough in what I want to
be displayed/summed from the data. I want column b summed for those rows for
the same text in column a and column c. If that makes it clear as mud.

"Marcelo" wrote:

Hi

try to use Sumproduct

=sumproduct(--($a$2:$a$1000=a1010)*($C$2:$c$1000=c1010);(b2:b100 0))

assuming that your data is on a2:c1000 and you will start the summary on
a1010.

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"novice" escreveu:

A twist on sumif?
I'm trying to sum column B by 2 conditions (columns A and C) and display the
summation and the 2 conditions that match. The example below is very short,
but there are approx. 100 conditions for column A and C that form many
comibinations. I need to display the conditions (that both need to be met)
with the summation.

For example (the data is much larger than this):
A B C
13-0506 Striping Contract 10.00 610
13-0506 Striping Contract 5.00 480
13-0506 Striping Contract 4.00 480
13-0501 Maint Job Inspection 2.00 610
13-0506 Striping Contract 2.00 610

Desired result:

A B C
13-0506 Striping Contract 12.00 610
13-0506 Striping Contract 9.00 480
13-0501 Maint Job Inspection 2.00 610

sum/group by 2 conditions
 

On Dec 13, 1:55 am, novice wrote:
Sorry big guy that didn't work. Maybe I wasn't clear enough in what I want to
be displayed/summed from the data. I want column b summed for those rows for
the same text in column a and column c. If that makes it clear as mud.

"Marcelo" wrote:
Hi

try to use Sumproduct

=sumproduct(--($a$2:$a$1000=a1010)*($C$2:$c$1000=c1010);(b2:b100 0))

assuming that your data is on a2:c1000 and you will start the summary on
a1010.

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo

"novice" escreveu:

A twist on sumif?
I'm trying to sum column B by 2 conditions (columns A and C) and display the
summation and the 2 conditions that match. The example below is very short,
but there are approx. 100 conditions for column A and C that form many
comibinations. I need to display the conditions (that both need to be met)
with the summation.

For example (the data is much larger than this):
A B C
13-0506 Striping Contract 10.00 610
13-0506 Striping Contract 5.00 480
13-0506 Striping Contract 4.00 480
13-0501 Maint Job Inspection 2.00 610
13-0506 Striping Contract 2.00 610

Desired result:

A B C
13-0506 Striping Contract 12.00 610
13-0506 Striping Contract 9.00 480
13-0501 Maint Job Inspection 2.00 610

put this formula in col D

=SUMPRODUCT(($A$2:$A$6=A2)*($C$2:$C$6=C2)*($B$2:$B $6))

sum/group by 2 conditions
 

That is a great help, shaqil, but can the function be changed to only display
one time if both conditions are met, and also display in the same row the
conditions that are being met. Will functions handle animals like this or do
I need to write a macro?

thanks for any help.

"shaqil" wrote:

On Dec 13, 1:55 am, novice wrote:
Sorry big guy that didn't work. Maybe I wasn't clear enough in what I want to
be displayed/summed from the data. I want column b summed for those rows for
the same text in column a and column c. If that makes it clear as mud.

"Marcelo" wrote:
Hi

try to use Sumproduct

=sumproduct(--($a$2:$a$1000=a1010)*($C$2:$c$1000=c1010);(b2:b100 0))

assuming that your data is on a2:c1000 and you will start the summary on
a1010.

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo

"novice" escreveu:

A twist on sumif?
I'm trying to sum column B by 2 conditions (columns A and C) and display the
summation and the 2 conditions that match. The example below is very short,
but there are approx. 100 conditions for column A and C that form many
comibinations. I need to display the conditions (that both need to be met)
with the summation.

For example (the data is much larger than this):
A B C
13-0506 Striping Contract 10.00 610
13-0506 Striping Contract 5.00 480
13-0506 Striping Contract 4.00 480
13-0501 Maint Job Inspection 2.00 610
13-0506 Striping Contract 2.00 610

Desired result:

A B C
13-0506 Striping Contract 12.00 610
13-0506 Striping Contract 9.00 480
13-0501 Maint Job Inspection 2.00 610

put this formula in col D

=SUMPRODUCT(($A$2:$A$6=A2)*($C$2:$C$6=C2)*($B$2:$B $6))