IF? OR SUMPRODUCT? or ????

  • Thread starter Thread starter Jerry Kinder
  • Start date Start date
J

Jerry Kinder

Hi All,

Col A5:A? conatins names of clients that repeat over time, Col B contains
codes " BW, COL, HC" AND OTHER CODES.

In Col D5:D? is a list of the names in Col A but listed only once, Column
hedding are in: Col E4 is BW, F4 is COL, G4 is HC.
Below each of these heddings next to the name in Col D i want to have a
count of how many times the code appers next to the corresponding name in
Col A.

The codes are intered in Col B like this: BW, HC or COL, BW, HC or HC, BW
or COL and so on, in other words they are entered in any order with a comma
between each code or only a single code only.

Hope this makes some sense.

Thanks,
JK
 
Hi jerry,
Just check out if =countif(range,criteria) function matches your desire.

Jerry said:
Hi All,

Col A5:A? conatins names of clients that repeat over time, Col B contains
codes " BW, COL, HC" AND OTHER CODES.

In Col D5:D? is a list of the names in Col A but listed only once, Column
hedding are in: Col E4 is BW, F4 is COL, G4 is HC.
Below each of these heddings next to the name in Col D i want to have a
count of how many times the code appers next to the corresponding name in
Col A.

The codes are intered in Col B like this: BW, HC or COL, BW, HC or HC, BW
or COL and so on, in other words they are entered in any order with a comma
between each code or only a single code only.

Hope this makes some sense.

Thanks,
JK
Click to expand...

--
"This isn't good or bad. It's just the way of things. Nothing stays the same.
" Real live preacher.

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-new/200801/1
 
Assuming that the codes in any cell col B are all unique. Meaning, you won't
have anything like this and expect a result of 3:

B5 = BW, BW, BW

=SUMPRODUCT(--($A$5:$A$20=$D5),--(ISNUMBER(SEARCH(E$4,$B$5:$B$20))))

Copy across then down.

If you do have entries like that above and do expect a result of 3 we'll
need a different formula.
 
Hi,
Does this help make it clearer. It is hard to put in words a sheet layout.

This is a attempt to lay out the work sheet:
Col A Col B Col F
ColE ColF ColG etc.
Down Down List of names down
one Code one Code OneCode
List of re-occuring Codes Only once each
In each of these columns will be the count
names.
from ColB of how many times each code appears

by the corrosponsing name in ColF and Col A
Thank you so for your efforts
Jerry Kinder
 
Back
Top