Calculate totals
 

Hi, Any help would be greatly appreciated.
Column A contains different divisions (there will several lines for each
divison).
If Col C1 contains a 1 then the amount in Col B1 is to be calculated for
each like division in Col A
Example:
First line Col A (1), B ($-7954.07), C (1)
Second line A (1), B ($1000.00), C (blank)
Third line A(1), B ($50.00), C (1),
In Col D a list of each division
Col E for the example above would be totals from line 1 and line 3
$-7904.07 which would go in Col E (Col D is a list of all the different
divisions)
And then so forth for each division.
Thanks
Donna

Calculate totals
 

Donna,

With headings in row1, insert this formula in E2, adjust ranges in column
A:C (same numeber of rows in all columns), and copy the formula down to all
divisions.

=SUMPRODUCT(--($A$2:$A$10=D2),--($C$2:$C$10=1),$B$2:$B$10)

Regards,
Per


"Donna" skrev i meddelelsen
...
Hi, Any help would be greatly appreciated.
Column A contains different divisions (there will several lines for each
divison).
If Col C1 contains a 1 then the amount in Col B1 is to be calculated for
each like division in Col A
Example:
First line Col A (1), B ($-7954.07), C (1)
Second line A (1), B ($1000.00), C (blank)
Third line A(1), B ($50.00), C (1),
In Col D a list of each division
Col E for the example above would be totals from line 1 and line 3
$-7904.07 which would go in Col E (Col D is a list of all the different
divisions)
And then so forth for each division.
Thanks
Donna

Calculate totals
 

Donna,
Using your example:
Column A Column B Column C Column D Column E
1 $-7,954.07 1 Name
1 $ 1,000.00 Name
1 $ 50.00 1 Name $-7,940.07

The formula in Cell E3 would be:
"=SUMIF(C1:C3,"=1",B1:B3)"
What this formula does - look in cells C1, C2, and C3 and see if it is equal
to 1.
If it is equal to 1, then adds the corresponding value in B together.

If each compnay name has its own number, just change the 1 to what ever
number matches the company.

hth

"Donna" wrote:

Hi, Any help would be greatly appreciated.
Column A contains different divisions (there will several lines for each
divison).
If Col C1 contains a 1 then the amount in Col B1 is to be calculated for
each like division in Col A
Example:
First line Col A (1), B ($-7954.07), C (1)
Second line A (1), B ($1000.00), C (blank)
Third line A(1), B ($50.00), C (1),
In Col D a list of each division
Col E for the example above would be totals from line 1 and line 3
$-7904.07 which would go in Col E (Col D is a list of all the different
divisions)
And then so forth for each division.
Thanks
Donna

Calculate totals
 

Hi,
I tried that and it worked, however I don't think I explained it very well.
I am using this in a macro. The number of lines in col A is random, there
may by 5 lines for Div 1 and 15 lines for div 2. What I would like to do is
if col C (meets criteria of a 1), then it would look in column A and all the
lines for div 1 that have a 1 in col C it would total in col E with the
corresponding div number in D.
Then it would look in Col C again and do the same thing for div. 2.
Sorry I was so confusing.
Donna

"FloMM2" wrote:

Donna,
Using your example:
Col A Col B Col C Col D Col E
Division Amount Meets Total Writeoff
Criteria Div
1 $-7,954.07 1 1 $-7,940.07
1 $ 1,000.00 2 $5.00
1 $ 50.00 1 3 0
2 $ 5.00 1
3 $25.00
The formula in Cell E3 would be:
"=SUMIF(C1:C3,"=1",B1:B3)"
What this formula does - look in cells C1, C2, and C3 and see if it is equal
to 1.
If it is equal to 1, then adds the corresponding value in B together.

If each compnay name has its own number, just change the 1 to what ever
number matches the company.

hth

"Donna" wrote:

Hi, Any help would be greatly appreciated.
Column A contains different divisions (there will several lines for each
divison).
If Col C1 contains a 1 then the amount in Col B1 is to be calculated for
each like division in Col A
Example:
First line Col A (1), B ($-7954.07), C (1)
Second line A (1), B ($1000.00), C (blank)
Third line A(1), B ($50.00), C (1),
In Col D a list of each division
Col E for the example above would be totals from line 1 and line 3
$-7904.07 which would go in Col E (Col D is a list of all the different
divisions)
And then so forth for each division.
Thanks
Donna