Sumproduct a column where 2 adj text columns contain same valu
Hi Biff,
Works a treat, many thanks.
"T. Valko" wrote:
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:A26E3),--(A3:A26F3),--(B3:B26=C3:C26),--(ISNUMBER(MATCH(B3:B26,G3:I3,0))))
--
Biff
Microsoft Excel MVP
"Struggling in Sheffield"
wrote in message ...
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" wrote:
Use cells to hold the criteria** :
E3 = 0
F3 = 1.01
G3 = Jones
H3 = Draper
I3 = Smith
=SUMPRODUCT(--(A3:A26E3),--(A3:A26F3),--(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:A260.00)*(A3:A261.01)*(B3:B26={ "Smith","Draper","Jones"}))
=SUMPRODUCT(--(A3:A26E3),--(A3:A26F3),--(ISNUMBER(MATCH(B3:B26,G3:I3,0))))
--
Biff
Microsoft Excel MVP
"Struggling in Sheffield"
wrote in message
...
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:A260.00)*(A3:A261.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.
|