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 B22
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 B211
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

 

[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]

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!)

 

[Mark McDonough]

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

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!)