SUMIFS with multiple criteria on different rows

commonproblems

New Member
Joined
Apr 25, 2018
Messages
7
Hi everyone,

i need help summing the population of birds whose age is greater than 60 weeks, on a row by row basis.

The data set is below:


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Population[/TD]
[TD]Age[/TD]
[TD]Eggs[/TD]
[TD]Feed[/TD]
[TD]Population[/TD]
[TD]Age[/TD]
[TD]Eggs[/TD]
[TD]Feed[/TD]
[TD]Population[/TD]
[TD]Age[/TD]
[TD]Eggs[/TD]
[TD]Feed[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2000[/TD]
[TD]76[/TD]
[TD]110[/TD]
[TD]100[/TD]
[TD]2000[/TD]
[TD]61[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]2000[/TD]
[TD]50[/TD]
[TD]100[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1990[/TD]
[TD]77[/TD]
[TD]120[/TD]
[TD]100[/TD]
[TD]1990[/TD]
[TD]62[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]1990[/TD]
[TD]51[/TD]
[TD]100[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1980[/TD]
[TD]78[/TD]
[TD]130[/TD]
[TD]100[/TD]
[TD]1980[/TD]
[TD]63[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]1980[/TD]
[TD]51[/TD]
[TD]100[/TD]
[TD]100[/TD]
[/TR]
</tbody>[/TABLE]


So the answer for Row 1 is 4,000. this is a simplistic data set. The actual data set is very long.

thank you for you help!

[TABLE="width: 1527"]
<colgroup><col><col><col span="15"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Hi everyone,

i need help summing the population of birds whose age is greater than 60 weeks, on a row by row basis.

The data set is below:


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Population[/TD]
[TD]Age[/TD]
[TD]Eggs[/TD]
[TD]Feed[/TD]
[TD]Population[/TD]
[TD]Age[/TD]
[TD]Eggs[/TD]
[TD]Feed[/TD]
[TD]Population[/TD]
[TD]Age[/TD]
[TD]Eggs[/TD]
[TD]Feed[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2000[/TD]
[TD]76[/TD]
[TD]110[/TD]
[TD]100[/TD]
[TD]2000[/TD]
[TD]61[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]2000[/TD]
[TD]50[/TD]
[TD]100[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1990[/TD]
[TD]77[/TD]
[TD]120[/TD]
[TD]100[/TD]
[TD]1990[/TD]
[TD]62[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]1990[/TD]
[TD]51[/TD]
[TD]100[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1980[/TD]
[TD]78[/TD]
[TD]130[/TD]
[TD]100[/TD]
[TD]1980[/TD]
[TD]63[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]1980[/TD]
[TD]51[/TD]
[TD]100[/TD]
[TD]100[/TD]
[/TR]
</tbody>[/TABLE]


So the answer for Row 1 is 4,000. this is a simplistic data set. The actual data set is very long.

thank you for you help!

[TABLE="width: 1527"]
<colgroup><col><col><col span="15"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Try this formula: =sumproduct(--(B2:J2>60), A2:I2)
 
Upvote 0
Hi Ahennema,

thank you for your reply.

that doesn't solve the problem. it returns a value of 4,557, which is the sum of A2 to H2.
 
Upvote 0
Hi StephenCrump,

Thank you as well for the reply.

When I've extrapolated the formula to the large data set, this didn't solve the issue either - sorry.
 
Upvote 0
When I've extrapolated the formula to the large data set, this didn't solve the issue either - sorry.

Not a problem for me.

But if you want further assistance, perhaps you'd like to give us more information about your data set? We have nothing to go on except for what you've posted.
 
Upvote 0
Hi StephenCrump

Here is a snip of the data:

https://www.dropbox.com/s/yfh4jrqqicln5qb/Capture.PNG?dl=0

I need to sum the population of the flocks aged more than 60 weeks old on a week by week basis. For the first week, Row 25, the answer should be the population of flock 49 (2,151) and the population of flock 50 (2,182), total = 4,333.

the answer to the second week, row 26, is flock 49 (2,147), flock 50 (2,178) and flock 51 (2,213) = 6,538


thank you
 
Upvote 0
First row: =SUMIFS(B25:R25,C$24:S$24,"Age (Wks)",C25:S25,">60")

Note this requires an exact match on all the headers, so probably slightly safer to use:

=SUMPRODUCT(B25:R25,--(TRIM(C$24:S$24)="Age (Wks)"),--(C25:S25>60))
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,215
Members
452,618
Latest member
Tam84

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top