software

Hi every body
I have this
Car CATEGORY SELL
FordA 5
BMWA 3
FordB 8
ToyotaA 1
FordC 4
FordA 7
BMWA 6
FordA 7
ToyotaB 5
FordA9
I have two questions
1) I want to count the Ford and Toyota whose category is 'A'.
2) Also i want to sum the total sell of Ford and Toyota with category 'A'.
Please solve this .
Thanks amp; regards
Nad

=sumproduct(--(a1:a10=quot;Fordquot;),--(b1:b10=quot;Aquot;))
sumproduct(--(a1:a10=quot;Toyotaquot;),--(b1:b10=quot;Aquot;)) '=== Count

=sumproduct(--(a1:a10=quot;Fordquot;),--(b1:b10=quot;Aquot;),(c1:c10))
sumproduct(--(a1:a10=quot;Toyotaquot;),--(b1:b10=quot;Aquot;),(c1:c10)) '=== sellquot;Nadquot; wrote:

gt; Hi every body
gt; I have this
gt; Car CATEGORY SELL
gt; FordA 5
gt; BMWA 3
gt; FordB 8
gt; ToyotaA 1
gt; FordC 4
gt; FordA 7
gt; BMWA 6
gt; FordA 7
gt; ToyotaB 5
gt; FordA9
gt; I have two questions
gt; 1) I want to count the Ford and Toyota whose category is 'A'.
gt; 2) Also i want to sum the total sell of Ford and Toyota with category 'A'.
gt; Please solve this .
gt; Thanks amp; regards
gt; Nad

thanks toppers
You are really topper.
i have another question
i want to count number of unique cars in range say A1:A10.
regards

quot;Toppersquot; wrote:

gt; =sumproduct(--(a1:a10=quot;Fordquot;),--(b1:b10=quot;Aquot;))
gt; sumproduct(--(a1:a10=quot;Toyotaquot;),--(b1:b10=quot;Aquot;)) '=== Count
gt;
gt; =sumproduct(--(a1:a10=quot;Fordquot;),--(b1:b10=quot;Aquot;),(c1:c10))
gt; sumproduct(--(a1:a10=quot;Toyotaquot;),--(b1:b10=quot;Aquot;),(c1:c10)) '=== sell
gt;
gt;
gt; quot;Nadquot; wrote:
gt;
gt; gt; Hi every body
gt; gt; I have this
gt; gt; Car CATEGORY SELL
gt; gt; FordA 5
gt; gt; BMWA 3
gt; gt; FordB 8
gt; gt; ToyotaA 1
gt; gt; FordC 4
gt; gt; FordA 7
gt; gt; BMWA 6
gt; gt; FordA 7
gt; gt; ToyotaB 5
gt; gt; FordA9
gt; gt; I have two questions
gt; gt; 1) I want to count the Ford and Toyota whose category is 'A'.
gt; gt; 2) Also i want to sum the total sell of Ford and Toyota with category 'A'.
gt; gt; Please solve this .
gt; gt; Thanks amp; regards
gt; gt; Nad

one way:

=SUMPRODUCT((A1:A10lt;gt;quot;quot;)/COUNTIF(A1:A10,A1:A10amp;quot;quot;))

(adjust the range to match, but don't use the whole column)

Nad wrote:
gt;
gt; thanks toppers
gt; You are really topper.
gt; i have another question
gt; i want to count number of unique cars in range say A1:A10.
gt; regards
gt;
gt; quot;Toppersquot; wrote:
gt;
gt; gt; =sumproduct(--(a1:a10=quot;Fordquot;),--(b1:b10=quot;Aquot;))
gt; gt; sumproduct(--(a1:a10=quot;Toyotaquot;),--(b1:b10=quot;Aquot;)) '=== Count
gt; gt;
gt; gt; =sumproduct(--(a1:a10=quot;Fordquot;),--(b1:b10=quot;Aquot;),(c1:c10))
gt; gt; sumproduct(--(a1:a10=quot;Toyotaquot;),--(b1:b10=quot;Aquot;),(c1:c10)) '=== sell
gt; gt;
gt; gt;
gt; gt; quot;Nadquot; wrote:
gt; gt;
gt; gt; gt; Hi every body
gt; gt; gt; I have this
gt; gt; gt; Car CATEGORY SELL
gt; gt; gt; Ford A 5
gt; gt; gt; BMW A 3
gt; gt; gt; Ford B 8
gt; gt; gt; Toyota A 1
gt; gt; gt; Ford C 4
gt; gt; gt; Ford A 7
gt; gt; gt; BMW A 6
gt; gt; gt; Ford A 7
gt; gt; gt; Toyota B 5
gt; gt; gt; Ford A 9
gt; gt; gt; I have two questions
gt; gt; gt; 1) I want to count the Ford and Toyota whose category is 'A'.
gt; gt; gt; 2) Also i want to sum the total sell of Ford and Toyota with category 'A'.
gt; gt; gt; Please solve this .
gt; gt; gt; Thanks amp; regards
gt; gt; gt; Nad

--

Dave Peterson

Ok Dave
Its fine. I got the result but i could not understand the statement
(=SUMPRODUCT((A1:A10lt;gt;quot;quot;)/COUNTIF(A1:A10,A1:A10amp;quot;quot;)).
will you please explain the statement.
if any other method to find.
Thanks amp; Regards
Nad

quot;Dave Petersonquot; wrote:

gt; one way:
gt;
gt; =SUMPRODUCT((A1:A10lt;gt;quot;quot;)/COUNTIF(A1:A10,A1:A10amp;quot;quot;))
gt;
gt; (adjust the range to match, but don't use the whole column)
gt;
gt; Nad wrote:
gt; gt;
gt; gt; thanks toppers
gt; gt; You are really topper.
gt; gt; i have another question
gt; gt; i want to count number of unique cars in range say A1:A10.
gt; gt; regards
gt; gt;
gt; gt; quot;Toppersquot; wrote:
gt; gt;
gt; gt; gt; =sumproduct(--(a1:a10=quot;Fordquot;),--(b1:b10=quot;Aquot;))
gt; gt; gt; sumproduct(--(a1:a10=quot;Toyotaquot;),--(b1:b10=quot;Aquot;)) '=== Count
gt; gt; gt;
gt; gt; gt; =sumproduct(--(a1:a10=quot;Fordquot;),--(b1:b10=quot;Aquot;),(c1:c10))
gt; gt; gt; sumproduct(--(a1:a10=quot;Toyotaquot;),--(b1:b10=quot;Aquot;),(c1:c10)) '=== sell
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Nadquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Hi every body
gt; gt; gt; gt; I have this
gt; gt; gt; gt; Car CATEGORY SELL
gt; gt; gt; gt; Ford A 5
gt; gt; gt; gt; BMW A 3
gt; gt; gt; gt; Ford B 8
gt; gt; gt; gt; Toyota A 1
gt; gt; gt; gt; Ford C 4
gt; gt; gt; gt; Ford A 7
gt; gt; gt; gt; BMW A 6
gt; gt; gt; gt; Ford A 7
gt; gt; gt; gt; Toyota B 5
gt; gt; gt; gt; Ford A 9
gt; gt; gt; gt; I have two questions
gt; gt; gt; gt; 1) I want to count the Ford and Toyota whose category is 'A'.
gt; gt; gt; gt; 2) Also i want to sum the total sell of Ford and Toyota with category 'A'.
gt; gt; gt; gt; Please solve this .
gt; gt; gt; gt; Thanks amp; regards
gt; gt; gt; gt; Nad
gt;
gt; --
gt;
gt; Dave Peterson
gt;

Try this in a new worksheet
Put a bunch of stuff in A1:A10 (duplicates ok--no empty cells)
Put this in B1:
=1/COUNTIF($A$1:$A$10,A1)
And drag down through b10
Now sum B1:B10

If a value occurs twice in the list, you'll see .5
If it occurs 3 times, you'll see .333333
If it only occurs once, you'll see 1.

This formula:
=SUMPRODUCT((A1:A10lt;gt;quot;quot;)/COUNTIF(A1:A10,A1:A10amp;quot;quot;))
essentially does this exact thing, but in one cell.

It also will fix any problems caused by empty cells. If you go back to your
test data and clear contents of one of the cells in A1:A10, you'll see #div/0
errors. (That's what the amp;quot;quot; will fix.)
Nad wrote:
gt;
gt; Ok Dave
gt; Its fine. I got the result but i could not understand the statement
gt; (=SUMPRODUCT((A1:A10lt;gt;quot;quot;)/COUNTIF(A1:A10,A1:A10amp;quot;quot;)).
gt; will you please explain the statement.
gt; if any other method to find.
gt; Thanks amp; Regards
gt; Nad
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; one way:
gt; gt;
gt; gt; =SUMPRODUCT((A1:A10lt;gt;quot;quot;)/COUNTIF(A1:A10,A1:A10amp;quot;quot;))
gt; gt;
gt; gt; (adjust the range to match, but don't use the whole column)
gt; gt;
gt; gt; Nad wrote:
gt; gt; gt;
gt; gt; gt; thanks toppers
gt; gt; gt; You are really topper.
gt; gt; gt; i have another question
gt; gt; gt; i want to count number of unique cars in range say A1:A10.
gt; gt; gt; regards
gt; gt; gt;
gt; gt; gt; quot;Toppersquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; =sumproduct(--(a1:a10=quot;Fordquot;),--(b1:b10=quot;Aquot;))
gt; gt; gt; gt; sumproduct(--(a1:a10=quot;Toyotaquot;),--(b1:b10=quot;Aquot;)) '=== Count
gt; gt; gt; gt;
gt; gt; gt; gt; =sumproduct(--(a1:a10=quot;Fordquot;),--(b1:b10=quot;Aquot;),(c1:c10))
gt; gt; gt; gt; sumproduct(--(a1:a10=quot;Toyotaquot;),--(b1:b10=quot;Aquot;),(c1:c10)) '=== sell
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Nadquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; Hi every body
gt; gt; gt; gt; gt; I have this
gt; gt; gt; gt; gt; Car CATEGORY SELL
gt; gt; gt; gt; gt; Ford A 5
gt; gt; gt; gt; gt; BMW A 3
gt; gt; gt; gt; gt; Ford B 8
gt; gt; gt; gt; gt; Toyota A 1
gt; gt; gt; gt; gt; Ford C 4
gt; gt; gt; gt; gt; Ford A 7
gt; gt; gt; gt; gt; BMW A 6
gt; gt; gt; gt; gt; Ford A 7
gt; gt; gt; gt; gt; Toyota B 5
gt; gt; gt; gt; gt; Ford A 9
gt; gt; gt; gt; gt; I have two questions
gt; gt; gt; gt; gt; 1) I want to count the Ford and Toyota whose category is 'A'.
gt; gt; gt; gt; gt; 2) Also i want to sum the total sell of Ford and Toyota with category 'A'.
gt; gt; gt; gt; gt; Please solve this .
gt; gt; gt; gt; gt; Thanks amp; regards
gt; gt; gt; gt; gt; Nad
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

--

Dave Peterson