Multiple Criteria for worksheet
 

If you can help figure this out, I would appreciate your help.

I am using this in a macro.
Col A is Division ( with Divisions numbers 1-100 but there is a random
number of lines for each division).
Col B is Amounts- debits and credits
Col C contains the number 1 in selected rows
Col D is a list of possible divisions 1-100 in numeric order
What I would like to do is the following:

Look at Column C and if there is a 1 in that cell, then sum the amounts from
Col B for each separate Division in Col A and put the sum in Col E beside the
corresponding division.
Thanks
Donna

Multiple Criteria for worksheet
 

Donna,

See my reply to your previous post, it is exactly the formyla you need.

Regards,
Per

"Donna" skrev i meddelelsen
...
If you can help figure this out, I would appreciate your help.

I am using this in a macro.
Col A is Division ( with Divisions numbers 1-100 but there is a random
number of lines for each division).
Col B is Amounts- debits and credits
Col C contains the number 1 in selected rows
Col D is a list of possible divisions 1-100 in numeric order
What I would like to do is the following:

Look at Column C and if there is a 1 in that cell, then sum the amounts
from
Col B for each separate Division in Col A and put the sum in Col E beside
the
corresponding division.
Thanks
Donna

Multiple Criteria for worksheet
 

Donna wrote:
If you can help figure this out, I would appreciate your help.

I am using this in a macro.
Col A is Division ( with Divisions numbers 1-100 but there is a random
number of lines for each division).
Col B is Amounts- debits and credits
Col C contains the number 1 in selected rows
Col D is a list of possible divisions 1-100 in numeric order
What I would like to do is the following:

Look at Column C and if there is a 1 in that cell, then sum the amounts from
Col B for each separate Division in Col A and put the sum in Col E beside the
corresponding division.
Thanks
Donna

In E1, fill down:

=SUMPRODUCT(--(D1=$A$1:$A$999),--($C$1:$C$999=1),$B$1:$B$999)

Another version:

=SUMPRODUCT((D1=$A$1:$A$999)*($C$1:$C$999=1)*$B$1: $B$999)

Multiple Criteria for worksheet
 

In E1, to get the first total for the Division listed in D1, try this formula:

=SUMPRODUCT(--($A$1:$A$1000=$D1),--($C$1:$C$1000=1),$B$1:$B$1000)

Then copy that formula down column E. Does that help?
--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Donna" wrote:

If you can help figure this out, I would appreciate your help.

I am using this in a macro.
Col A is Division ( with Divisions numbers 1-100 but there is a random
number of lines for each division).
Col B is Amounts- debits and credits
Col C contains the number 1 in selected rows
Col D is a list of possible divisions 1-100 in numeric order
What I would like to do is the following:

Look at Column C and if there is a 1 in that cell, then sum the amounts from
Col B for each separate Division in Col A and put the sum in Col E beside the
corresponding division.
Thanks
Donna

Multiple Criteria for worksheet
 

Thank you soooo much! It worked great!!!
Donna

"JBeaucaire" wrote:

In E1, to get the first total for the Division listed in D1, try this formula:

=SUMPRODUCT(--($A$1:$A$1000=$D1),--($C$1:$C$1000=1),$B$1:$B$1000)

Then copy that formula down column E. Does that help?
--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Donna" wrote:

If you can help figure this out, I would appreciate your help.

I am using this in a macro.
Col A is Division ( with Divisions numbers 1-100 but there is a random
number of lines for each division).
Col B is Amounts- debits and credits
Col C contains the number 1 in selected rows
Col D is a list of possible divisions 1-100 in numeric order
What I would like to do is the following:

Look at Column C and if there is a 1 in that cell, then sum the amounts from
Col B for each separate Division in Col A and put the sum in Col E beside the
corresponding division.
Thanks
Donna