Repost - Using SUMIF and COUNTA

Here's one response ..

Assuming your sample data below is in A1:F4
[col header in row1, data from row2 down]

PrdA...PrdB...Col C......Col D.......Col E...Col F
100....500....person a..person b...50.....250
100....500....person a....................100....500
100....500....person b..person a...50.....250

To calculate the numbers in cols E and F
---------------------------------------------
Instead of the formulas you posted, try the shorter:

Put in E2: =A2/COUNTA($C2:$D2)

Copy E2 down to E4, then copy across to F4
(or copy E2 across to F2, then down to F4)

Now to compute for col K
----------------------------
Assume the table continuation snippet below is in J1:K4

Col J (table starts)...Col K
person a....................400
person b....................300
person c........................0

With the names listed down in col J,

Put in K2: =SUMIF(C$2$4,J2,E$2:F$4)

*Array-enter* the formula in K2, i.e.
hold down CTRL and SHIFT, press ENTER
(instead of just pressing ENTER alone)

Excel will wrap curly braces {} around the formula, viz.:
{=SUMIF(C$2$4,J2,E$2:F$4)}

Do not enter the curly braces yourself !

The array-entering of the formula above
is probably what you missed ..

Now just drag to copy the array formula in K2 down to K4
(in the normal manner)

Col K will return the totals for the corresponding names in col J

As long as you insert rows in-between,
i.e. after the first but before the last row
the array SUMIF formulas in col K will auto-adjust accordingly
to accomodate the insertions

--
Rgds
Max
xl 97
--
Please respond, in newsgroup
xdemechanik atyahoodotcom
---
"Peter" wrote

I'm not sure what it means when nobody answers, nor if a repost is
appropriate. So, I guess I'll just repost and keep my fingers crossed. I
apologize if this isn't appropriate!

Original
I recently got a request to total up throughput for different product
types (Product A$ & B$) by individual (Col's C & D). The only rule added is
that if more than one person worked on this project (i.e. Col C and Col D
are populated with different names), then the dollar amount for whatever
product you are looking at gets divided by the number of people who worked
on it. For simplicity, I only show two. However, I used counta and divide
by counta if there were every more than two. There is also an extra
tidbit added at the end to spice up this request a bit...but I would like to
ask this portion first. Anyway, the data looks like this:

Product A$ Product B$ Col C Col D Col E
Col F.............Col J (table starts) Col K
100 500 person a person b formula
formula formula
100 500 person a
100 500 person b person a

I got this to work, but it is a bit burdensome. I used this formula in
column e..... if(counta(c1:d1)=1,a1,a1/(counta(c1:d1))....that return the
dollar amount that each person would get for product a$. I did the same
thing in column F for product b$. Then, I created a table of names which
represent the persons in Col C & D, let's say starting at J. Then, in the
column next to the name, I chose to put that individuals total for product
a$. To get that total, I used.....
=SUMIF(C1:C3,J1,E1:E3)+SUMIF(D13,J1,E1:E3).....T hat got me what I
needed, by also took away the flexibility to add the potential for three
people working together. Using CountA had that flexibility built in, but I
couldn't get it to work. Also, something weird (I think) happened when I
had the sumif covering C13. It recognized person a's name and knew it had
to assign a value, but when it assigned the value, if person a showed up in
column C, it gave column E's value (as expected). If person a, showed up in
column D, it gave the value for column F. I tried to used absolute on
E1:E3, but it didn't change anything. It still summed outside the zone I
specified.....weird.

Anyway, I know it can be done this way....but where did I go wrong with
doing this an easier way???

Also, the next piece.....the person came back and said they will want to
use this as a running list. So, I had to be able to allow rows to be
inserted. I chose a macro to insert a row and copy the formulas up a row.
Worked out ok....had I known that up front, what would be your
recommendations? Pivot tables are a stretch for me on this one as I have
very very little knowledge of them, but it seems this might be the optimum
choice here. If so, please walk me through what I should have done.

Thank you in advance.....sorry about the lengthy note....you can probably
imagine how long I spent on getting my results from the above
methodology....yikes!

Have a great day everyone.

Peter

Max,

Thank you for the help....but I still get the same issue that caused me to break up the sumif's...I don't want to add the values in column's e & f. I want to report each separately as they represent different products.

When I enter the formula as an array, it adds dollars outside of the range I specify....

A b c d e f
100 200 pete patty 50 100
200 400 pete 200 400

For product A, patty's total should be 0....using the array, it reports back 100. It seems that if the lookup value (patty) occurs in the second column of the lookup range (column d), it doesn't return column e's value, it returns column f's value.

Any ideas?

Thank you again and have a great day.

Peter

----- Max wrote: -----

Here's one response ..

Assuming your sample data below is in A1:F4
[col header in row1, data from row2 down]

PrdA...PrdB...Col C......Col D.......Col E...Col F
100....500....person a..person b...50.....250
100....500....person a....................100....500
100....500....person b..person a...50.....250

To calculate the numbers in cols E and F
---------------------------------------------
Instead of the formulas you posted, try the shorter:

Put in E2: =A2/COUNTA($C2:$D2)

Copy E2 down to E4, then copy across to F4
(or copy E2 across to F2, then down to F4)

Now to compute for col K
----------------------------
Assume the table continuation snippet below is in J1:K4

Col J (table starts)...Col K
person a....................400
person b....................300
person c........................0

With the names listed down in col J,

Put in K2: =SUMIF(C$2$4,J2,E$2:F$4)

*Array-enter* the formula in K2, i.e.
hold down CTRL and SHIFT, press ENTER
(instead of just pressing ENTER alone)

Excel will wrap curly braces {} around the formula, viz.:
{=SUMIF(C$2$4,J2,E$2:F$4)}

Do not enter the curly braces yourself !

The array-entering of the formula above
is probably what you missed ..

Now just drag to copy the array formula in K2 down to K4
(in the normal manner)

Col K will return the totals for the corresponding names in col J

As long as you insert rows in-between,
i.e. after the first but before the last row
the array SUMIF formulas in col K will auto-adjust accordingly
to accomodate the insertions

--
Rgds
Max
xl 97
--
Please respond, in newsgroup
xdemechanik atyahoodotcom
---
"Peter" wrote

I'm not sure what it means when nobody answers, nor if a repost is
appropriate. So, I guess I'll just repost and keep my fingers crossed. I
apologize if this isn't appropriate!
Original
I recently got a request to total up throughput for different product
types (Product A$ & B$) by individual (Col's C & D). The only rule added is
that if more than one person worked on this project (i.e. Col C and Col D
are populated with different names), then the dollar amount for whatever
product you are looking at gets divided by the number of people who worked
on it. For simplicity, I only show two. However, I used counta and divide
by counta if there were every more than two. There is also an extra
tidbit added at the end to spice up this request a bit...but I would like to
ask this portion first. Anyway, the data looks like this:
Product A$ Product B$ Col C Col D Col E
Col F.............Col J (table starts) Col K
100 500 person a person b formula
formula formula
100 500 person a
100 500 person b person a
I got this to work, but it is a bit burdensome. I used this formula in
column e..... if(counta(c1:d1)=1,a1,a1/(counta(c1:d1))....that return the
dollar amount that each person would get for product a$. I did the same
thing in column F for product b$. Then, I created a table of names which
represent the persons in Col C & D, let's say starting at J. Then, in the
column next to the name, I chose to put that individuals total for product
a$. To get that total, I used.....
=SUMIF(C1:C3,J1,E1:E3)+SUMIF(D13,J1,E1:E3).....T hat got me what I
needed, by also took away the flexibility to add the potential for three
people working together. Using CountA had that flexibility built in, but I
couldn't get it to work. Also, something weird (I think) happened when I
had the sumif covering C13. It recognized person a's name and knew it had
to assign a value, but when it assigned the value, if person a showed up in
column C, it gave column E's value (as expected). If person a, showed up in
column D, it gave the value for column F. I tried to used absolute on
E1:E3, but it didn't change anything. It still summed outside the zone I
specified.....weird.
Anyway, I know it can be done this way....but where did I go wrong with
doing this an easier way???
Also, the next piece.....the person came back and said they will want to
use this as a running list. So, I had to be able to allow rows to be
inserted. I chose a macro to insert a row and copy the formulas up a row.
Worked out ok....had I known that up front, what would be your
recommendations? Pivot tables are a stretch for me on this one as I have
very very little knowledge of them, but it seems this might be the optimum
choice here. If so, please walk me through what I should have done.
Thank you in advance.....sorry about the lengthy note....you can probably
imagine how long I spent on getting my results from the above
methodology....yikes!
Have a great day everyone.
Peter

You're welcome ..

Perhaps you could post the original formulas which you tried
and where these formulas are .. Then it'll be clearer

I may have mis-read your original post ..

--
Rgds
Max
xl 97
--
Please respond, in newsgroup
xdemechanik atyahoodotcom
---
"Peter" wrote in message
...
Max,

Thank you for the help....but I still get the same issue that caused me to
break up the sumif's...I don't want to add the values in column's e & f. I
want to report each separately as they represent different products.

When I enter the formula as an array, it adds dollars outside of the range
I specify....

A b c d e f
100 200 pete patty 50 100
200 400 pete 200 400

For product A, patty's total should be 0....using the array, it reports
back 100. It seems that if the lookup value (patty) occurs in the second
column of the lookup range (column d), it doesn't return column e's value,
it returns column f's value.

Any ideas?

Thank you again and have a great day.

Peter

Max,

I apologize. I'm certain it was me in my original post. The very original formula I used was one similar to the one you requested. I didn't array it, but the 'guts' were the same. The formulas I have now that works a

=SUMIF(C1:C3,J1,E1:E3)+SUMIF(D13,J1,E1:E3)
and the other formula is
=SUMIF(C1:C3,J1,F1:F3)+SUMIF(D13,J1,F1:F3).....

using J1 = patty and the data is

A b c d e f
100 200 pete patty 50 100
200 400 pete 200 400

I get Patty for column e = $50, and patty for column f = $100.

I am not well versed in arrays, but I thought that if I used {=SUMIF(C13,J1,E1:E3)}, I would get Patty for e = $50, but I get $100. It seems that if patty is in column d, it does not use the corresponding value in column e. Rather, it uses the corresponding value shown in column f. I gotta' be doing something wrong, but I can't figure it out.

Thank you in advanace again!

Peter


----- Max wrote: -----

You're welcome ..

Perhaps you could post the original formulas which you tried
and where these formulas are .. Then it'll be clearer

I may have mis-read your original post ..

--
Rgds
Max
xl 97
--
Please respond, in newsgroup
xdemechanik atyahoodotcom
---
"Peter" wrote in message
...
Max,
Thank you for the help....but I still get the same issue that caused me to
break up the sumif's...I don't want to add the values in column's e & f. I
want to report each separately as they represent different products.
When I enter the formula as an array, it adds dollars outside of the range
I specify....
A b c d e f
100 200 pete patty 50 100
200 400 pete 200 400
For product A, patty's total should be 0....using the array, it reports
back 100. It seems that if the lookup value (patty) occurs in the second
column of the lookup range (column d), it doesn't return column e's value,
it returns column f's value.
Any ideas?
Thank you again and have a great day.
Peter

Sorry, please dismiss the array SUMIF suggested earlier which does not work
(Incorrect results).

I don't think you did anything wrong, the behaviour observed is per your
post.

Guess I'm out of further suggestions on how to collapse, for example:
=SUMIF(C1:C3,J1,E1:E3)+SUMIF(D13,J1,E1:E3)
given your set-up

Maybe just stick with the tried and tested
SUMIF() + SUMIF() since this is working well ?

Perhaps others might step in here with other options for you

Good luck!

--
Rgds
Max
xl 97
---
Please respond, in newsgroup
xdemechanik atyahoodotcom
----
"Peter" wrote in message
...
Max,

I apologize. I'm certain it was me in my original post. The very
original formula I used was one similar to the one you requested. I didn't
array it, but the 'guts' were the same. The formulas I have now that works
a

=SUMIF(C1:C3,J1,E1:E3)+SUMIF(D13,J1,E1:E3)
and the other formula is
=SUMIF(C1:C3,J1,F1:F3)+SUMIF(D13,J1,F1:F3).....

using J1 = patty and the data is

A b c d e f
100 200 pete patty 50 100
200 400 pete 200 400

I get Patty for column e = $50, and patty for column f = $100.

I am not well versed in arrays, but I thought that if I used
{=SUMIF(C13,J1,E1:E3)}, I would get Patty for e = $50, but I get $100. It
seems that if patty is in column d, it does not use the corresponding value
in column e. Rather, it uses the corresponding value shown in column f. I
gotta' be doing something wrong, but I can't figure it out.

Thank you in advanace again!

Peter