Sumproduct a column where 2 adj text columns contain same value

[Struggling in Sheffield]

Hi,
I'm using a sumproduct formula to ascertain the number of times that a value
between 0 and 1 occurs in column A (range A3:A26), where the adjacent cell in
column B (B3:B26) contains either "Smith", "Draper" or "Jones":

=SUMPRODUCT((A3:A26>0.00)*(A3:A26<1.01)*(B3:B26={"Smith","Draper","Jones"}))

This formula works fine and details the number of entries where the value in
column A is between 0 and 1, and the name in the adjacent column B cell is
Smith Draper or Jones.

What I need to do is the same SUMPRODUCT of 0-1 entries in column A but this
time where the names listed in column B are equal to more names listed in
Column C (i.e. where B7 = "Jones" and C7 = "Jones", 1 will be the added to my
SUMPRODUCT total (if the value in A7 is between 0 and 1).

Keep skirting around the edges of this one without being quite able to nail
it.
Any pointers gratefully received.
Cheers,
Steve.

 

[T. Valko]

Use cells to hold the criteria** :

E3 = 0
F3 = 1.01
G3 = Jones
H3 = Draper
I3 = Smith

=SUMPRODUCT(--(A3:A26>E3),--(A3:A26<F3),--(B3:B26=G3),--(C3:C26=G3))

** Use cells to hold the criteria:

You'd have to redo this formula since array constants can't use cell
references.

=SUMPRODUCT((A3:A26>0.00)*(A3:A26<1.01)*(B3:B26={"Smith","Draper","Jones"}))

=SUMPRODUCT(--(A3:A26>E3),--(A3:A26<F3),--(ISNUMBER(MATCH(B3:B26,G3:I3,0))))

 

[Struggling in Sheffield]

Hi,
I get the gist of what you're saying but can't see how B3:B26 is being
compared to C3:C26 for the three names?

A B C
3 0.12 Smith Jones
4 2.03 Draper Draper
5 0.65 Jones Jones
6 0.81 Smith Smith
7 0.33 Jones Draper
8 1.52 Smith Smith
9 0.74 Jones Jones
10 3.02 Draper Jones

For the table illustrated I need my formula to return the value of "3".

A5 has a value less than 1 (0.65), whilst B5 & C5 have the same name "Jones"
(count 1)
A6 has a value less than 1 (0.81), whilst B6 & C6 have the same name "Smith"
(count 2)
A9 has a value less than 1 (0.74), whilst B9 & C9 have the same name "Jones"
(count 3)

Whilst names match on other rows or the value in column A is less than 1,
only three times do all these criteria line up and this is what I need to
count.
Cheers,
Steve.

 

[T. Valko]

I misunderstood your requirement.

I thought you just wanted to match Jones and Jones.

Try this:

E3 = 0
F3 = 1.01
G3 = Jones
H3 = Draper
I3 = Smith

=SUMPRODUCT(--(A3:A26>E3),--(A3:A26<F3),--(B3:B26=C3:C26),--(ISNUMBER(MATCH(B3:B26,G3:I3,0))))

 

[Struggling in Sheffield]

Hi Biff,
Works a treat, many thanks.

I misunderstood your requirement.

I thought you just wanted to match Jones and Jones.

Try this:

E3 = 0
F3 = 1.01
G3 = Jones
H3 = Draper
I3 = Smith

=SUMPRODUCT(--(A3:A26>E3),--(A3:A26<F3),--(B3:B26=C3:C26),--(ISNUMBER(MATCH(B3:B26,G3:I3,0))))

 

[T. Valko]

You're welcome. Thanks for the feedback!