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
<tbody>
</tbody>
Sheet2: SUMS
<tbody>
</tbody>
****
Sheet2: SUMS - Correct totals:
<tbody>
</tbody>
****
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
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
| number | set1 | set2 | tosum | set3 |
| 111 | x | a | 1 | q |
| 222 | x | b | 2 | q |
| 333 | x | c | 3 | q |
| 111 | y | a | 1 | |
| 333 | y | b | 1 | q |
| 111 | x | c | 1 | |
| 222 | x | a | 2 | q |
| 222 | y | b | 2 | q |
| 333 | y | c | 2 | q |
<tbody>
</tbody>
Sheet2: SUMS
| number | sum |
| 111 | |
| 222 | |
| 333 |
<tbody>
</tbody>
****
Sheet2: SUMS - Correct totals:
| number | sum |
| 111 | 1 |
| 222 | 2 |
| 333 | 0 |
<tbody>
</tbody>
****
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
