Formula help!!!

[Guest]

I have a worksheet w/column named "Reject reason" in Column A2:B10.
D & E collumn must be filled automatically when I entered "REJECT REASON" &
"COMP?"(see (2)=).
------------------------------------------------------------------------------------------------
(1)=

A B D
E
"Code #" "Rejects Reasons" "Comp A" "Comp B"
1 Contract
2 Ethnic Channels
3 Install probs
4 NFL
5 No Pitch
6 No Computer
7 Other Programming not avail
8 Owes us money
9 Previous service problems
10 Price too high


-------------------------------------------------------------------------
(2)=

In a cell range H10:I20, I have the Following:These values I have to entered
myself.
H I
"REJECT REASON" "COMP?"
1 A
5 A
10 B
7 A
3 B
5 B
1 A
5 B

-----------------------------------------------------------------------------------------

Ok, The collums D & E must count how many "Reject reasons" for each A 0r B
Comp?...



In this case it should look like this:
A B D
E
"Code #" "Rejects Reasons" "Comp A" "Comp B"
1 Contract 2
2 Ethnic Channels
3 Install probs
1
4 NFL
5 No Pitch 1 2
6 No Computer
7 Other Programming not avail 1
8 Owes us money
9 Previous service problems
10 Price too high
1

----------------------------------------------------------------------------------------------

I looks simple, but I just don't know how to do it.
Pls help me.



Thanks all.

 

[Guest]

Assuming your source table is within A1:B11
with the data you enter within H10:I20 (in the same sheet)

First, put in/change the col labels in C11 to just: A, B
Then place in C2:
=SUMPRODUCT(($I$10:$I$20=C$1)*($H$10:$H$20=$A2))
Copy C2 to D2, fill down to D11

 

[Guest]

Max ,

I made couple changes and it works great.
Thank you very much.
I noticed that on the cells where nothing is entered a zero shows up. How do
I get rid of if? I need something to make the cells C and D blank when no
text is input in the H:I range.
That will be my last request.

thanks max.

 

[Max]

1. Easiest option is to suppress extraneous zeros display in the sheet:
Click Tools > Options > View tab
Uncheck "Zero values" > OK, done!

2. An alternative is to use a simple IF** construct, eg:
=IF(SUMPRODUCT(($I$10:$I$20=C$1)*($H$10:$H$20=$A2))=0,"",SUMPRODUCT(($I$10:$I$20=C$1)*($H$10:$H$20=$A2)))

**the downside is this will increase the calc load and may affect
performance especially for heavy duty functions like sumproduct where large
ranges are involved. Although in your case here, it shouldn't be material
since the ranges are small.

Your choice ..

 

[Harlan Grove]

2. An alternative is to use a simple IF** construct, eg:
=IF(SUMPRODUCT(($I$10:$I$20=C$1)*($H$10:$H$20=$A2))=0,"",
SUMPRODUCT(($I$10:$I$20=C$1)*($H$10:$H$20=$A2)))

**the downside is this will increase the calc load . . .

....

Looks like all that needs testing is whether there's anything in H10:I20, so
all that may take is

=IF(COUNTA($H$10:$I$20),SUMPRODUCT(($I$10:$I$20=C$1)*($H$10:$H$20=$A2)),"")

which should have much less impact on recalc speed.