Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
multiple criteria from single worksheet | Excel Discussion (Misc queries) | |||
Find text in another worksheet when multiple criteria are met | Excel Worksheet Functions | |||
Multiple DSUM criteria (MONTH & a numeric value) between worksheet | Excel Worksheet Functions | |||
Lookup 2 criteria across multiple worksheet (Attn: Peo Sjoblom) | Excel Worksheet Functions | |||
Select rows of data in a worksheet on one criteria in multiple co | Excel Worksheet Functions |