Need help with Excel Formula

[Bob Barker]

I am new, so bear with me. I have Excel 2003. I have started a spreadsheet
with the following parameters:

Rows 1-15 (clients) have monetary values for various items represented by
Columns H - L (commissions and other moneys due).
Each Row has a total of these values in Column M (total moneys involved per
client)
Each Column has a total of these values in Row 16 (total for each column for
that month plus total overall)
Pretty basic I know.....

What I need to do is add Column N that when I type in a specific alphabetic
entry that it will take all the monetary values from that row and transfer
them to other rows located at 17-19.

These new rows would be labeled: "DONE" "PENDING" "DENIED" and "PAID" so
hopefully you can see what I want. I want a total of all business, which I
already have, but I also want to sort and keep track of the status of each
item just by typing in one of the four words mentioned above. These new rows
would also need to keep a running total of all the items that have the
corresponding label. Meaning all "DONE" labeled clients would show up in the
"DONE" row with the totals showing.

I do not know if I have been clear enough, please ask and I will do my best
to answer.....

 

[JLatham]

Bob,
Alright, let us start by putting your categories into Cells N17, N18, N19
and N20 as
DONE
PENDING
DENIED
PAID
The order doesn't matter, just that you have them in those 4 cells.
In H17 put this formula:
=SUMPRODUCT(--(H$1:H$15),--($N$1:$N$15=$N17))
You can now fill/copy that formula across into columns I, J, K and L, and
then you can also fill/copy them on down through row 20. Then as you put the
various descriptions in column N in rows 1 through 15, the different
subtotals will appear in rows 17-20.

I hope that helps with your problem.

Here's how this works. SUMPRODUCT() calculates the SUM [result of addition]
of the PRODUCTS [result from multiplication] based on the parameters in the
formula. It automatically works through the rows mentioned in the formula
one at a time calculating the PRODUCT of those parameters, keeping a running
total of the SUM of those products. The way the formula is set up, the first
parameter
--(H$1:H$15) simply picks up the value in each row in column H to be
multiplied by the result of the second parameter --($N$1:$N$15=$N17) when an
entry in column N, on a given row matches the $N17 (or $N18, $N19, $N20 later
on) the result is either zero (no match) or 1 (a match with the category).
So for each row the product is either zero or the value in the first column
mentioned (H, I, J, K or L) and those results are added together to be
displayed in the cells with the SUMPRODUCT() formula in them. The order of
parameters in it aren't important, we could have written the first one as:
=SUMPRODUCT(--($N$1:$N$15=$N17),--(H$1:H$15))
and obtained the same results. We also did not have to put the key words in
column N on rows 17-20, we could have put them elsewhere and just changed the
=test to use that column on rows 17-20 instead of column N as we did.

 

[Bob Barker]

I am not sure this is what I was looking to do. Let me try to explain it a
little differently. I will give a small example of the format I want to use.

1st comm 2nd comm residual bonus total
(new column)

Cust A $100 $150 $50 $0 $300
PAID

Cust B $150 $100 $100 $50 $400
PAID

Cust D $0 $50 $150 $0
$200 PENDING

Cust E $50 $100 $100 $100 $350
PENDING

Totals $300 $400 $400 $150 $1250


(new rows)
Total Paid $250 $250 $150 $50 $700
Total Pend $50 $150 $250 $100 $550
Total Done $0 $0 $0 $0
$0

This is more what I want. The new column alphabetic entry will fill in the
new row data based on which word I type into that column. Everytime I update
the spreadsheet by filling in the accounts status (Paid, done, etc) is
automatically moves the financial data to the appropriate row for that status
while still keeping the original totals. Can this be done?

Bob,
Alright, let us start by putting your categories into Cells N17, N18, N19
and N20 as
DONE
PENDING
DENIED
PAID
The order doesn't matter, just that you have them in those 4 cells.
In H17 put this formula:
=SUMPRODUCT(--(H$1:H$15),--($N$1:$N$15=$N17))
You can now fill/copy that formula across into columns I, J, K and L, and
then you can also fill/copy them on down through row 20. Then as you put the
various descriptions in column N in rows 1 through 15, the different
subtotals will appear in rows 17-20.

I hope that helps with your problem.

Here's how this works. SUMPRODUCT() calculates the SUM [result of addition]
of the PRODUCTS [result from multiplication] based on the parameters in the
formula. It automatically works through the rows mentioned in the formula
one at a time calculating the PRODUCT of those parameters, keeping a running
total of the SUM of those products. The way the formula is set up, the first
parameter
--(H$1:H$15) simply picks up the value in each row in column H to be
multiplied by the result of the second parameter --($N$1:$N$15=$N17) when an
entry in column N, on a given row matches the $N17 (or $N18, $N19, $N20 later
on) the result is either zero (no match) or 1 (a match with the category).
So for each row the product is either zero or the value in the first column
mentioned (H, I, J, K or L) and those results are added together to be
displayed in the cells with the SUMPRODUCT() formula in them. The order of
parameters in it aren't important, we could have written the first one as:
=SUMPRODUCT(--($N$1:$N$15=$N17),--(H$1:H$15))
and obtained the same results. We also did not have to put the key words in
column N on rows 17-20, we could have put them elsewhere and just changed the
=test to use that column on rows 17-20 instead of column N as we did.

I am new, so bear with me. I have Excel 2003. I have started a spreadsheet
with the following parameters:

Rows 1-15 (clients) have monetary values for various items represented by
Columns H - L (commissions and other moneys due).
Each Row has a total of these values in Column M (total moneys involved per
client)
Each Column has a total of these values in Row 16 (total for each column for
that month plus total overall)
Pretty basic I know.....

What I need to do is add Column N that when I type in a specific alphabetic
entry that it will take all the monetary values from that row and transfer
them to other rows located at 17-19.

These new rows would be labeled: "DONE" "PENDING" "DENIED" and "PAID" so
hopefully you can see what I want. I want a total of all business, which I
already have, but I also want to sort and keep track of the status of each
item just by typing in one of the four words mentioned above. These new rows
would also need to keep a running total of all the items that have the
corresponding label. Meaning all "DONE" labeled clients would show up in the
"DONE" row with the totals showing.

I do not know if I have been clear enough, please ask and I will do my best
to answer.....

 

[Bob Barker]

The preceding post didn't format the way I typed it.....

The fifth column over is the total of the values in the 4 previous columns
for that row.

The sixth colum is where I want Paid, Pending, etc. to go...hence the title
(new Column) that started on the next row in this post.

I am not sure this is what I was looking to do. Let me try to explain it a
little differently. I will give a small example of the format I want to use.

1st comm 2nd comm residual bonus total
(new column)

Cust A $100 $150 $50 $0 $300
PAID

Cust B $150 $100 $100 $50 $400
PAID

Cust D $0 $50 $150 $0
$200 PENDING

Cust E $50 $100 $100 $100 $350
PENDING

Totals $300 $400 $400 $150 $1250


(new rows)
Total Paid $250 $250 $150 $50 $700
Total Pend $50 $150 $250 $100 $550
Total Done $0 $0 $0 $0
$0

This is more what I want. The new column alphabetic entry will fill in the
new row data based on which word I type into that column. Everytime I update
the spreadsheet by filling in the accounts status (Paid, done, etc) is
automatically moves the financial data to the appropriate row for that status
while still keeping the original totals. Can this be done?

Bob,
Alright, let us start by putting your categories into Cells N17, N18, N19
and N20 as
DONE
PENDING
DENIED
PAID
The order doesn't matter, just that you have them in those 4 cells.
In H17 put this formula:
=SUMPRODUCT(--(H$1:H$15),--($N$1:$N$15=$N17))
You can now fill/copy that formula across into columns I, J, K and L, and
then you can also fill/copy them on down through row 20. Then as you put the
various descriptions in column N in rows 1 through 15, the different
subtotals will appear in rows 17-20.

I hope that helps with your problem.

Here's how this works. SUMPRODUCT() calculates the SUM [result of addition]
of the PRODUCTS [result from multiplication] based on the parameters in the
formula. It automatically works through the rows mentioned in the formula
one at a time calculating the PRODUCT of those parameters, keeping a running
total of the SUM of those products. The way the formula is set up, the first
parameter
--(H$1:H$15) simply picks up the value in each row in column H to be
multiplied by the result of the second parameter --($N$1:$N$15=$N17) when an
entry in column N, on a given row matches the $N17 (or $N18, $N19, $N20 later
on) the result is either zero (no match) or 1 (a match with the category).
So for each row the product is either zero or the value in the first column
mentioned (H, I, J, K or L) and those results are added together to be
displayed in the cells with the SUMPRODUCT() formula in them. The order of
parameters in it aren't important, we could have written the first one as:
=SUMPRODUCT(--($N$1:$N$15=$N17),--(H$1:H$15))
and obtained the same results. We also did not have to put the key words in
column N on rows 17-20, we could have put them elsewhere and just changed the
=test to use that column on rows 17-20 instead of column N as we did.

I am new, so bear with me. I have Excel 2003. I have started a spreadsheet
with the following parameters:

Rows 1-15 (clients) have monetary values for various items represented by
Columns H - L (commissions and other moneys due).
Each Row has a total of these values in Column M (total moneys involved per
client)
Each Column has a total of these values in Row 16 (total for each column for
that month plus total overall)
Pretty basic I know.....

What I need to do is add Column N that when I type in a specific alphabetic
entry that it will take all the monetary values from that row and transfer
them to other rows located at 17-19.

These new rows would be labeled: "DONE" "PENDING" "DENIED" and "PAID" so
hopefully you can see what I want. I want a total of all business, which I
already have, but I also want to sort and keep track of the status of each
item just by typing in one of the four words mentioned above. These new rows
would also need to keep a running total of all the items that have the
corresponding label. Meaning all "DONE" labeled clients would show up in the
"DONE" row with the totals showing.

I do not know if I have been clear enough, please ask and I will do my best
to answer.....

 

[JLatham]

I think maybe it does. To make things clearer, I've prepared a workbook so
that you can see it in action. Here is a link to the workbook so you can
examine it.
http://www.jlathamsite.com/uploads/for_BobBarker.xls

If you still have questions or I've misunderstood things, by all means let
me know.

The preceding post didn't format the way I typed it.....

The fifth column over is the total of the values in the 4 previous columns
for that row.

The sixth colum is where I want Paid, Pending, etc. to go...hence the title
(new Column) that started on the next row in this post.

I am not sure this is what I was looking to do. Let me try to explain it a
little differently. I will give a small example of the format I want to use.

1st comm 2nd comm residual bonus total
(new column)

Cust A $100 $150 $50 $0 $300
PAID

Cust B $150 $100 $100 $50 $400
PAID

Cust D $0 $50 $150 $0
$200 PENDING

Cust E $50 $100 $100 $100 $350
PENDING

Totals $300 $400 $400 $150 $1250


(new rows)
Total Paid $250 $250 $150 $50 $700
Total Pend $50 $150 $250 $100 $550
Total Done $0 $0 $0 $0
$0

This is more what I want. The new column alphabetic entry will fill in the
new row data based on which word I type into that column. Everytime I update
the spreadsheet by filling in the accounts status (Paid, done, etc) is
automatically moves the financial data to the appropriate row for that status
while still keeping the original totals. Can this be done?

Bob,
Alright, let us start by putting your categories into Cells N17, N18, N19
and N20 as
DONE
PENDING
DENIED
PAID
The order doesn't matter, just that you have them in those 4 cells.
In H17 put this formula:
=SUMPRODUCT(--(H$1:H$15),--($N$1:$N$15=$N17))
You can now fill/copy that formula across into columns I, J, K and L, and
then you can also fill/copy them on down through row 20. Then as you put the
various descriptions in column N in rows 1 through 15, the different
subtotals will appear in rows 17-20.

I hope that helps with your problem.

Here's how this works. SUMPRODUCT() calculates the SUM [result of addition]
of the PRODUCTS [result from multiplication] based on the parameters in the
formula. It automatically works through the rows mentioned in the formula
one at a time calculating the PRODUCT of those parameters, keeping a running
total of the SUM of those products. The way the formula is set up, the first
parameter
--(H$1:H$15) simply picks up the value in each row in column H to be
multiplied by the result of the second parameter --($N$1:$N$15=$N17) when an
entry in column N, on a given row matches the $N17 (or $N18, $N19, $N20 later
on) the result is either zero (no match) or 1 (a match with the category).
So for each row the product is either zero or the value in the first column
mentioned (H, I, J, K or L) and those results are added together to be
displayed in the cells with the SUMPRODUCT() formula in them. The order of
parameters in it aren't important, we could have written the first one as:
=SUMPRODUCT(--($N$1:$N$15=$N17),--(H$1:H$15))
and obtained the same results. We also did not have to put the key words in
column N on rows 17-20, we could have put them elsewhere and just changed the
=test to use that column on rows 17-20 instead of column N as we did.

:

I am new, so bear with me. I have Excel 2003. I have started a spreadsheet
with the following parameters:

Rows 1-15 (clients) have monetary values for various items represented by
Columns H - L (commissions and other moneys due).
Each Row has a total of these values in Column M (total moneys involved per
client)
Each Column has a total of these values in Row 16 (total for each column for
that month plus total overall)
Pretty basic I know.....

What I need to do is add Column N that when I type in a specific alphabetic
entry that it will take all the monetary values from that row and transfer
them to other rows located at 17-19.

These new rows would be labeled: "DONE" "PENDING" "DENIED" and "PAID" so
hopefully you can see what I want. I want a total of all business, which I
already have, but I also want to sort and keep track of the status of each
item just by typing in one of the four words mentioned above. These new rows
would also need to keep a running total of all the items that have the
corresponding label. Meaning all "DONE" labeled clients would show up in the
"DONE" row with the totals showing.

I do not know if I have been clear enough, please ask and I will do my best
to answer.....