What formula do I use to count if value in other column?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

LoriT - 13 Nov 2006 03:46 GMT

I need to count if value in one column is met & if value in other column is >0
A B C
Trailer Bike County
1.00 adam
1.00 adam
1.00 mara

Count is how many times unit sold to this county for each type

Reply to this Message

Stefi - 13 Nov 2006 08:18 GMT

If you mean to fill in such a table

County    Trailer    Bike
adam    1    1
mara    1    0

then use this formula in B2 and drag it tothe right and down as required!

=SUMIF(Munka1!$C:$C,$A2,Munka1!A:A)

Regards,
Stefi

„LoriT” ezt írta:

> I need to count if value in one column is met & if value in other column is >0
> A B C
[quoted text clipped - 4 lines]
>
> Count is how many times unit sold to this county for each type

Reply to this Message

LoriT - 13 Nov 2006 17:11 GMT

Let me be more clear, info comes from sales table where trailer sale is
600.00, but I want it to count as 1 sale of a trailer for county of adam. I
also need to count 2 trailer columns for new & used. But count only sales in
trailer column that are a specific county.
Thank you

> If you mean to fill in such a table
>
[quoted text clipped - 19 lines]
> >
> > Count is how many times unit sold to this county for each type

Reply to this Message

Stefi - 14 Nov 2006 08:48 GMT

If your original table (Sheet1) is something like this
Trailer Bike County
600 adam
400 adam
800 mara

then in sheet2
County    Trailer    Bike
adam    1    1
mara    1    0

=SUMPRODUCT(--(Sheet1!$C$2:$C$4=$A2),--(Sheet1!A$2:A$4>0))

entered in B2 and copied to the right and down gives the required result.
You have to adjust ranges!!!

Regards,
Stefi

„LoriT” ezt írta:

> Let me be more clear, info comes from sales table where trailer sale is
> 600.00, but I want it to count as 1 sale of a trailer for county of adam. I
[quoted text clipped - 25 lines]
> > >
> > > Count is how many times unit sold to this county for each type

Reply to this Message

LoriT - 15 Nov 2006 03:41 GMT

Thank you very much! It does exactly what I needed!
Lori

> If your original table (Sheet1) is something like this
> Trailer Bike County
[quoted text clipped - 46 lines]
> > > >
> > > > Count is how many times unit sold to this county for each type

Reply to this Message

Stefi - 15 Nov 2006 07:42 GMT

You are welcome! Thanks for the feedback!
Stefi

„LoriT” ezt írta:

> Thank you very much! It does exactly what I needed!
> Lori
[quoted text clipped - 49 lines]
> > > > >
> > > > > Count is how many times unit sold to this county for each type

Reply to this Message