Countif Problem would like a sumproduct solution

[Mark McDonough]

I'm having a bit of trouble with countif. Sumif no problem. Here is the
situation I have.

In one column there are a list of brands, in the next 3 columns are 3
suppliers rates for each brand. Some suppliers have chosen to not complete
rates for where they do not operate so in their columns, there are zeros to
indicated no rates submitted for those particular brands. Here is a
rudimentary layout.

Brand List Supplier1 Supplier2 Supplier 3
ABC Site1
ABC Site2
ABC Site3
ABC Site4
ABC Site5
AAF Site1
AAF Site2
AAF Site3
AAF Site4
AAF Site5
Under Supplier columns all the rates are submitted but suppose Supplier 2
can only service 2 ABCs and not all 5. When the sum by Supplier is done
Supplier 2 can appear cheap but then not really because he has only quoted
on 3 ABC sites.

What I want to do is to get the sum of the rates and then divide through by
the count within each brands quoted so that other suppliers who quoted on
all ABCs are not disadvantaged and you can compare like with like. The sumif
part works fine. It's the countif that I'm having the problem with. I can
get the count of all sites by brand using countif but not the count of sites
where only some of them have been quoted. My list of sites runs into the
thousands - this is a great simplification of what I mean

Maybe a well constructed sumproduct formula may do the job. If all sites
have been quoted on then sum the rates by brand and divide through by the
count of sites for each supplier. Where fewer than all sites are quoted on,
then sum those rates by brand and divide through by the count of the sites
within that brand that have been quoted.

Any help appreciated

[Bernard Liengme]

I worked with just the 10 records from you dataset
I assumed the first Brand (ABC Site1) was in A2, the supplier data in B2:D2
In H1:J1 I copied supplier names
In H2, I used =SUMIF($A$2:$A$11,"ABC*",B2:B11) to find the SUM of ABC's for
Supplier 1
In H3 for the count I used
=SUMPRODUCT(--(LEFT($A$2:$A$11,3)="ABC"), --(B2:B11>0))
In H4 =H1/H2
These formulas seem to give correct values based on some arbitrary numbers I
entered in B2:D11
Of course, on could combine them as
=SUMIF($A$2:$A$11,"ABC*",B2:B11)/SUMPRODUCT(--(LEFT($A$2:$A$11,3)="ABC"), --(B2:B11>0))
For explanation of SUMPRODUCT see
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
http://mcgimpsey.com/excel/formulae/doubleneg.html
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Mark McDonough" <[email protected]> wrote in message
news:[email protected]...
> I'm having a bit of trouble with countif. Sumif no problem. Here is the
> situation I have.
>
> In one column there are a list of brands, in the next 3 columns are 3
> suppliers rates for each brand. Some suppliers have chosen to not complete
> rates for where they do not operate so in their columns, there are zeros
> to indicated no rates submitted for those particular brands. Here is a
> rudimentary layout.
>
> Brand List Supplier1 Supplier2 Supplier 3
> ABC Site1
> ABC Site2
> ABC Site3
> ABC Site4
> ABC Site5
> AAF Site1
> AAF Site2
> AAF Site3
> AAF Site4
> AAF Site5
> Under Supplier columns all the rates are submitted but suppose Supplier 2
> can only service 2 ABCs and not all 5. When the sum by Supplier is done
> Supplier 2 can appear cheap but then not really because he has only quoted
> on 3 ABC sites.
>
> What I want to do is to get the sum of the rates and then divide through
> by the count within each brands quoted so that other suppliers who quoted
> on all ABCs are not disadvantaged and you can compare like with like. The
> sumif part works fine. It's the countif that I'm having the problem with.
> I can get the count of all sites by brand using countif but not the count
> of sites where only some of them have been quoted. My list of sites runs
> into the thousands - this is a great simplification of what I mean
>
> Maybe a well constructed sumproduct formula may do the job. If all sites
> have been quoted on then sum the rates by brand and divide through by the
> count of sites for each supplier. Where fewer than all sites are quoted
> on, then sum those rates by brand and divide through by the count of the
> sites within that brand that have been quoted.
>
> Any help appreciated
>
>
>
>
>

[Mark McDonough]

Can the "ABC" part of the formula be substited for the cell reference
containing "ABC". Thanks for this info - I'll give it a whirl later today.


"Bernard Liengme" <[email protected]> wrote in message
news:%[email protected]...
>I worked with just the 10 records from you dataset
> I assumed the first Brand (ABC Site1) was in A2, the supplier data in
> B2:D2
> In H1:J1 I copied supplier names
> In H2, I used =SUMIF($A$2:$A$11,"ABC*",B2:B11) to find the SUM of ABC's
> for Supplier 1
> In H3 for the count I used
> =SUMPRODUCT(--(LEFT($A$2:$A$11,3)="ABC"), --(B2:B11>0))
> In H4 =H1/H2
> These formulas seem to give correct values based on some arbitrary numbers
> I entered in B2:D11
> Of course, on could combine them as
> =SUMIF($A$2:$A$11,"ABC*",B2:B11)/SUMPRODUCT(--(LEFT($A$2:$A$11,3)="ABC"),
> --(B2:B11>0))
> For explanation of SUMPRODUCT see
> http://www.xldynamic.com/source/xld.SUMPRODUCT.html
> http://mcgimpsey.com/excel/formulae/doubleneg.html
> best wishes
> --
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "Mark McDonough" <[email protected]> wrote in message
> news:[email protected]...
>> I'm having a bit of trouble with countif. Sumif no problem. Here is the
>> situation I have.
>>
>> In one column there are a list of brands, in the next 3 columns are 3
>> suppliers rates for each brand. Some suppliers have chosen to not
>> complete rates for where they do not operate so in their columns, there
>> are zeros to indicated no rates submitted for those particular brands.
>> Here is a rudimentary layout.
>>
>> Brand List Supplier1 Supplier2 Supplier 3
>> ABC Site1
>> ABC Site2
>> ABC Site3
>> ABC Site4
>> ABC Site5
>> AAF Site1
>> AAF Site2
>> AAF Site3
>> AAF Site4
>> AAF Site5
>> Under Supplier columns all the rates are submitted but suppose Supplier 2
>> can only service 2 ABCs and not all 5. When the sum by Supplier is done
>> Supplier 2 can appear cheap but then not really because he has only
>> quoted on 3 ABC sites.
>>
>> What I want to do is to get the sum of the rates and then divide through
>> by the count within each brands quoted so that other suppliers who quoted
>> on all ABCs are not disadvantaged and you can compare like with like. The
>> sumif part works fine. It's the countif that I'm having the problem with.
>> I can get the count of all sites by brand using countif but not the count
>> of sites where only some of them have been quoted. My list of sites runs
>> into the thousands - this is a great simplification of what I mean
>>
>> Maybe a well constructed sumproduct formula may do the job. If all sites
>> have been quoted on then sum the rates by brand and divide through by the
>> count of sites for each supplier. Where fewer than all sites are quoted
>> on, then sum those rates by brand and divide through by the count of the
>> sites within that brand that have been quoted.
>>
>> Any help appreciated
>>
>>
>>
>>
>>
>
>
>

[Bernard Liengme]

I put the text ABC* in G2 and used =SUMIF($A$2:$A$11,$G$2,B2:B11) in H2 and
copied it across to I2 and J2 and got the correct values
In G3 I entered the text ABC and in H3 used
=SUMPRODUCT(--(LEFT($A$2:$A$11,3)=$G3), --(B2:B11>0)) which I copied to I3
and J 3 and again all is OK
To save entering the 'key' twice, in H3 I tried
=SUMPRODUCT(--(LEFT($A$2:$A$11,3)=LEFT($G3,3)), --(B2:B11>0)) which worked
just fine
best wishes (sorry for late reply - I was painting the house today, it's
great being retired!)
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Mark McDonough" <[email protected]> wrote in message
news:[email protected]...
> Can the "ABC" part of the formula be substited for the cell reference
> containing "ABC". Thanks for this info - I'll give it a whirl later today.
>
>
> "Bernard Liengme" <[email protected]> wrote in message
> news:%[email protected]...
>>I worked with just the 10 records from you dataset
>> I assumed the first Brand (ABC Site1) was in A2, the supplier data in
>> B2:D2
>> In H1:J1 I copied supplier names
>> In H2, I used =SUMIF($A$2:$A$11,"ABC*",B2:B11) to find the SUM of ABC's
>> for Supplier 1
>> In H3 for the count I used
>> =SUMPRODUCT(--(LEFT($A$2:$A$11,3)="ABC"), --(B2:B11>0))
>> In H4 =H1/H2
>> These formulas seem to give correct values based on some arbitrary
>> numbers I entered in B2:D11
>> Of course, on could combine them as
>>
>> SUMIF($A$2:$A$11,"ABC*",B2:B11)/SUMPRODUCT(--(LEFT($A$2:$A$11,3)="ABC"),
>> --(B2:B11>0))
>> For explanation of SUMPRODUCT see
>> http://www.xldynamic.com/source/xld.SUMPRODUCT.html
>> http://mcgimpsey.com/excel/formulae/doubleneg.html
>> best wishes
>> --
>> Bernard V Liengme
>> www.stfx.ca/people/bliengme
>> remove caps from email
>>
>> "Mark McDonough" <[email protected]> wrote in message
>> news:[email protected]...
>>> I'm having a bit of trouble with countif. Sumif no problem. Here is the
>>> situation I have.
>>>
>>> In one column there are a list of brands, in the next 3 columns are 3
>>> suppliers rates for each brand. Some suppliers have chosen to not
>>> complete rates for where they do not operate so in their columns, there
>>> are zeros to indicated no rates submitted for those particular brands.
>>> Here is a rudimentary layout.
>>>
>>> Brand List Supplier1 Supplier2 Supplier 3
>>> ABC Site1
>>> ABC Site2
>>> ABC Site3
>>> ABC Site4
>>> ABC Site5
>>> AAF Site1
>>> AAF Site2
>>> AAF Site3
>>> AAF Site4
>>> AAF Site5
>>> Under Supplier columns all the rates are submitted but suppose Supplier
>>> 2 can only service 2 ABCs and not all 5. When the sum by Supplier is
>>> done Supplier 2 can appear cheap but then not really because he has only
>>> quoted on 3 ABC sites.
>>>
>>> What I want to do is to get the sum of the rates and then divide through
>>> by the count within each brands quoted so that other suppliers who
>>> quoted on all ABCs are not disadvantaged and you can compare like with
>>> like. The sumif part works fine. It's the countif that I'm having the
>>> problem with. I can get the count of all sites by brand using countif
>>> but not the count of sites where only some of them have been quoted. My
>>> list of sites runs into the thousands - this is a great simplification
>>> of what I mean
>>>
>>> Maybe a well constructed sumproduct formula may do the job. If all sites
>>> have been quoted on then sum the rates by brand and divide through by
>>> the count of sites for each supplier. Where fewer than all sites are
>>> quoted on, then sum those rates by brand and divide through by the count
>>> of the sites within that brand that have been quoted.
>>>
>>> Any help appreciated
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>
>

[Mark McDonough]

Thanks guys..............works well.


"Bernard Liengme" <[email protected]> wrote in message
news:[email protected]...
>I put the text ABC* in G2 and used =SUMIF($A$2:$A$11,$G$2,B2:B11) in H2 and
>copied it across to I2 and J2 and got the correct values
> In G3 I entered the text ABC and in H3 used
> =SUMPRODUCT(--(LEFT($A$2:$A$11,3)=$G3), --(B2:B11>0)) which I copied to I3
> and J 3 and again all is OK
> To save entering the 'key' twice, in H3 I tried
> =SUMPRODUCT(--(LEFT($A$2:$A$11,3)=LEFT($G3,3)), --(B2:B11>0)) which worked
> just fine
> best wishes (sorry for late reply - I was painting the house today, it's
> great being retired!)
> --
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "Mark McDonough" <[email protected]> wrote in message
> news:[email protected]...
>> Can the "ABC" part of the formula be substited for the cell reference
>> containing "ABC". Thanks for this info - I'll give it a whirl later
>> today.
>>
>>
>> "Bernard Liengme" <[email protected]> wrote in message
>> news:%[email protected]...
>>>I worked with just the 10 records from you dataset
>>> I assumed the first Brand (ABC Site1) was in A2, the supplier data in
>>> B2:D2
>>> In H1:J1 I copied supplier names
>>> In H2, I used =SUMIF($A$2:$A$11,"ABC*",B2:B11) to find the SUM of ABC's
>>> for Supplier 1
>>> In H3 for the count I used
>>> =SUMPRODUCT(--(LEFT($A$2:$A$11,3)="ABC"), --(B2:B11>0))
>>> In H4 =H1/H2
>>> These formulas seem to give correct values based on some arbitrary
>>> numbers I entered in B2:D11
>>> Of course, on could combine them as
>>>
>>>
>>> UMIF($A$2:$A$11,"ABC*",B2:B11)/SUMPRODUCT(--(LEFT($A$2:$A$11,3)="ABC"),
>>> --(B2:B11>0))
>>> For explanation of SUMPRODUCT see
>>> http://www.xldynamic.com/source/xld.SUMPRODUCT.html
>>> http://mcgimpsey.com/excel/formulae/doubleneg.html
>>> best wishes
>>> --
>>> Bernard V Liengme
>>> www.stfx.ca/people/bliengme
>>> remove caps from email
>>>
>>> "Mark McDonough" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> I'm having a bit of trouble with countif. Sumif no problem. Here is the
>>>> situation I have.
>>>>
>>>> In one column there are a list of brands, in the next 3 columns are 3
>>>> suppliers rates for each brand. Some suppliers have chosen to not
>>>> complete rates for where they do not operate so in their columns, there
>>>> are zeros to indicated no rates submitted for those particular brands.
>>>> Here is a rudimentary layout.
>>>>
>>>> Brand List Supplier1 Supplier2 Supplier 3
>>>> ABC Site1
>>>> ABC Site2
>>>> ABC Site3
>>>> ABC Site4
>>>> ABC Site5
>>>> AAF Site1
>>>> AAF Site2
>>>> AAF Site3
>>>> AAF Site4
>>>> AAF Site5
>>>> Under Supplier columns all the rates are submitted but suppose Supplier
>>>> 2 can only service 2 ABCs and not all 5. When the sum by Supplier is
>>>> done Supplier 2 can appear cheap but then not really because he has
>>>> only quoted on 3 ABC sites.
>>>>
>>>> What I want to do is to get the sum of the rates and then divide
>>>> through by the count within each brands quoted so that other suppliers
>>>> who quoted on all ABCs are not disadvantaged and you can compare like
>>>> with like. The sumif part works fine. It's the countif that I'm having
>>>> the problem with. I can get the count of all sites by brand using
>>>> countif but not the count of sites where only some of them have been
>>>> quoted. My list of sites runs into the thousands - this is a great
>>>> simplification of what I mean
>>>>
>>>> Maybe a well constructed sumproduct formula may do the job. If all
>>>> sites have been quoted on then sum the rates by brand and divide
>>>> through by the count of sites for each supplier. Where fewer than all
>>>> sites are quoted on, then sum those rates by brand and divide through
>>>> by the count of the sites within that brand that have been quoted.
>>>>
>>>> Any help appreciated
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>
>>
>
>
>