=SUMIF - Multiple Pages, Multiple Conditions *PROBABLY SIMPLE!*

[sd2173]

Good evening all,


So what I am trying to accomplish is summing data from one sheet (DATA for example), on a second sheet (SUMS for example) if specific conditions are met on the data sheet.


I also want to make sure I am summing the data for only the matching number on the SUMS sheet. There will be multiple entries with the same identifying number on the DATA sheet, but only one row for each on the SUMS sheet.


Essentially what I am trying to develop is a formula to drag down the B colum on my SUMS sheet that will do the following for example.


*If SUMS!a2=DATA!A:A and
*DATA!B:B="X"
*DATA!C:C<>"C" and
*DATA!C:C<>"B" and
*DATA!E:E="Q"


Sum the values of DATA!D:D in SUMS!B2 down according to the matching number in the A column of sums. Also making sure if the conditions were not met it sums a "0" not a false or anything.


Below is an example of the sheet layouts and then a "Correct totals" example.

Sheet1: DATA
[TABLE="width: 320"]
<tbody>[TR]
[TD="width: 64"]number[/TD]
[TD="width: 64"]set1[/TD]
[TD="width: 64"]set2[/TD]
[TD="width: 64"]tosum[/TD]
[TD="width: 64"]set3[/TD]
[/TR]
[TR]
[TD="align: right"]111[/TD]
[TD]x[/TD]
[TD]a[/TD]
[TD="align: right"]1[/TD]
[TD]q[/TD]
[/TR]
[TR]
[TD="align: right"]222[/TD]
[TD]x[/TD]
[TD]b[/TD]
[TD="align: right"]2[/TD]
[TD]q[/TD]
[/TR]
[TR]
[TD="align: right"]333[/TD]
[TD]x[/TD]
[TD]c[/TD]
[TD="align: right"]3[/TD]
[TD]q[/TD]
[/TR]
[TR]
[TD="align: right"]111[/TD]
[TD]y[/TD]
[TD]a[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]333[/TD]
[TD]y[/TD]
[TD]b[/TD]
[TD="align: right"]1[/TD]
[TD]q[/TD]
[/TR]
[TR]
[TD="align: right"]111[/TD]
[TD]x[/TD]
[TD]c[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]222[/TD]
[TD]x[/TD]
[TD]a[/TD]
[TD="align: right"]2[/TD]
[TD]q[/TD]
[/TR]
[TR]
[TD="align: right"]222[/TD]
[TD]y[/TD]
[TD]b[/TD]
[TD="align: right"]2[/TD]
[TD]q[/TD]
[/TR]
[TR]
[TD="align: right"]333[/TD]
[TD]y[/TD]
[TD]c[/TD]
[TD="align: right"]2[/TD]
[TD]q[/TD]
[/TR]
</tbody>[/TABLE]

Sheet2: SUMS
[TABLE="width: 128"]
<tbody>[TR]
[TD="width: 64"]number[/TD]
[TD="width: 64"]sum[/TD]
[/TR]
[TR]
[TD="align: right"]111[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]222[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]333[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

****


Sheet2: SUMS - Correct totals:
[TABLE="width: 128"]
<tbody>[TR]
[TD="width: 64"]number[/TD]
[TD="width: 64"]sum[/TD]
[/TR]
[TR]
[TD="align: right"]111[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]222[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]333[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]


****


The formula below I've tried putting together which I speculate is way off simply returns 0's in my B:B column in SUMS when placed in B2 and dragged down.


=SUMIF(DATA!D:D,(AND(SUMS!A2=DATA!A:A, DATA!B:B="X",DATA!C:C<>"B",DATA!C:C<>"C",DATA!E:E="Q")),DATA!D:D)


Not having a whole lot of experience with =SUMIF and especially across sheets with multiple conditions I even tried with just one condition with forumla below but same result.


=SUMIF(DATA!D:D,(SUMS!A2=DATA!A:A),DATA!D:D)


It is frustrating because I feel like this should be a simple task and that I have the right idea going in but can't seem to make it work.


I am very thankful for any feedback and suggestions where I may be going astray in my attempts at developing a working formula.


Thank you for your time.


Regards,
SD

 

[vogel997]

[TABLE="width: 989"]
<colgroup><col width="97" style="width: 73pt; mso-width-source: userset; mso-width-alt: 3547;"> <col width="64" style="width: 48pt;"> <col width="1157" style="width: 868pt; mso-width-source: userset; mso-width-alt: 42313;"> <tbody>[TR]
[TD="width: 97, bgcolor: white"]number[/TD]
[TD="width: 64, bgcolor: white"]sum[/TD]
[TD="width: 1157, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 97, bgcolor: white"]111[/TD]
[TD="width: 64, bgcolor: white"]1[/TD]
[TD="bgcolor: transparent"]=SUMPRODUCT(--(A2=Data!$A$2:$A$10),--(Data!$B$2:$B$10="x"),--(Data!$C$2:$C$10<>"c"),--(Data!$C$2:$C$10<>"b"),--(Data!$E$2:$E$10="q"),Data!$D$2:$D$10)[/TD]
[/TR]
[TR]
[TD="width: 97, bgcolor: white"]222[/TD]
[TD="width: 64, bgcolor: white"]2[/TD]
[TD="bgcolor: transparent"]=SUMPRODUCT(--(A3=Data!$A$2:$A$10),--(Data!$B$2:$B$10="x"),--(Data!$C$2:$C$10<>"c"),--(Data!$C$2:$C$10<>"b"),--(Data!$E$2:$E$10="q"),Data!$D$2:$D$10)[/TD]
[/TR]
[TR]
[TD="width: 97, bgcolor: white"]333[/TD]
[TD="width: 64, bgcolor: white"]0[/TD]
[TD="bgcolor: transparent"]=SUMPRODUCT(--(A4=Data!$A$2:$A$10),--(Data!$B$2:$B$10="x"),--(Data!$C$2:$C$10<>"c"),--(Data!$C$2:$C$10<>"b"),--(Data!$E$2:$E$10="q"),Data!$D$2:$D$10)[/TD]
[/TR]
</tbody>[/TABLE]

 

[sd2173]

This looks to suggest I would need to do a different formula for each row in SUMS, is there a formula to paste down, possibly a vLOOKUP that would alleviate this requirement? Else with the actual amount of rows in the SUMS page this would be a lot of work, also, if more than 10 values, can the formulas simply be changed from A2=Data!$A$2:$A$10 to A2=Data!$A$2:$A$A for the respective columns?

 

[sd2173]

Sorry I see now my confusion and this is a formula that can be dragged, I was just confused since you showed it for each cell, which is correct I just was in a different mindset I guess when reading it on the site and not doing in Excel. Much appreciated it seems like it should work great in some trials I am going through now!



Thanks again!

SD

 

[vogel997]

[TABLE="width: 989"]
<colgroup><col width="97" style="width: 73pt; mso-width-source: userset; mso-width-alt: 3547;"> <col width="64" style="width: 48pt;"> <col width="1157" style="width: 868pt; mso-width-source: userset; mso-width-alt: 42313;"> <tbody>[TR]
[TD="width: 97, bgcolor: white"]number[/TD]
[TD="width: 64, bgcolor: white"]sum[/TD]
[TD="width: 1157, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 97, bgcolor: white"]111[/TD]
[TD="width: 64, bgcolor: white"]1[/TD]
[TD="bgcolor: transparent"]=SUMPRODUCT(--(A2=Data!$A:$A),--(Data!$B:$B="x"),--(Data!$C:$C<>"c"),--(Data!$C:$C<>"b"),--(Data!$E:$E="q"),Data!$D:$D)[/TD]
[/TR]
[TR]
[TD="width: 97, bgcolor: white"]222[/TD]
[TD="width: 64, bgcolor: white"]2[/TD]
[TD="bgcolor: transparent"]=SUMPRODUCT(--(A3=Data!$A:$A),--(Data!$B:$B="x"),--(Data!$C:$C<>"c"),--(Data!$C:$C<>"b"),--(Data!$E:$E="q"),Data!$D:$D)[/TD]
[/TR]
[TR]
[TD="width: 97, bgcolor: white"]333[/TD]
[TD="width: 64, bgcolor: white"]0[/TD]
[TD="bgcolor: transparent"]=SUMPRODUCT(--(A4=Data!$A:$A),--(Data!$B:$B="x"),--(Data!$C:$C<>"c"),--(Data!$C:$C<>"b"),--(Data!$E:$E="q"),Data!$D:$D)[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]It works also with colums only and you can drag it.[/TD]
[/TR]
</tbody>[/TABLE]