COUNTIF

[Malik Nadeem]

i want to know how to calculate the following things

A B C D
12345 20GP ALMAR MTY
67892 40GP JV ILD
11111 40HC RSL O/H
22222 40GP FLORENCE SOLD
12345 40GP ALMAR ILD
67892 20GP JV O/H
11111 40HC RSL MTY
22222 40HC FLORENCE ILD
NEED THE RESULT IN FOLLOWING CONDITION

ALMAR
20GP 40GP 40HC
----------------------------------------------
MTY 1 1 0
ILD 1 0 0
O/H 0 0 0

FLORENCE.
20GP 40GP 40HC
----------------------------------------------
..
..
..
..

RSL
20GP 40GP 40HC
----------------------------------------------
..
..
..
..

JV
20GP 40GP 40HC
----------------------------------------------
..
..
..
..
..

if understand kindly help me out.

regards
Malik Nadeem

 

[Don Guillett]

Your example does not compute as there are only 2 ALMAR in the table.
Where g12 =Almar
h12=20gp
g13=mty
=SUMPRODUCT(($H$2:$H$9=H$12)*($I$2:$I$9=$G$12)*($J$2:$J$9=$G13))
ALMAR 20GP 40GP 40HC
MTY 1 0 0
ILD 0 1 0
O/H 0 0 0

 

[Malik Nadeem]

DEAR Don Guillett

THANKS FOR YOUR GUIDANCE YES YOU ARE RIGHT THERE WAS ONLY 2 ALMAR SHOWING
THAT WAS TYPO MISTAKE FROM MY SIDE

HOW EVER IT CAN SOLVE MY PROBLEM BUT I WANT TO KNOW IS THERE ANY RESULT WE
CAN GET WITH THE HELP OF =COUNTIF FORMULA OR FUNCTION?
IF YES KINDLY ADVISE THE SAME.
THANKS IN ADVANCE FOR YOUR GREAT SUPPORT AN HELP.

REGARDS
MALIK NADEEM



waiting for your reply.

========================

 

[David Biddulph]

Please get your caps lock key repaired so that you can stop SHOUTING.

 

[Malik Nadeem]

Dear Don

further on below msg can i sent you the main excel file beacuse i am unable
to do that through this formula can you please tell me how to i gave you the
file you can see that problem which i m facing.
waiting for your reply.

 

[Malik Nadeem]

Dear David,

sorry for this.

is there any way from i can transfer my excel file in which i have lot of
data it will be much better for you people to understand
below help of don is good and helpful for me but when i m applying same
condition on my main file it is not working some time result FALS some some 0
or -
what sould i do can you please help me out to fix this problem?

you great support in this regards will be highly appreciated.

regards
Malik Nadeem

 

[Malik Nadeem]

file is uploaded on
http://www.mediafire.com/?sharekey=be0ceac1e6b8edc2d2db6fb9a8902bda

kindly check and revert

 

[Don Guillett]

Send to my address below along with a clear explanation and before/after
examples

 

[Don Guillett]

=SUMPRODUCT((RSL!$E$3:$E$848=$B$10)*(RSL!$C$3:$C$848=B$11)*(RSL!$S$3:$S$848=$A12))
defined names to make self adjusting
=SUMPRODUCT((RSLcolE=$B$10)*(RSLcolC=B$11)*(RSLcolS=$A20))