Sumproduct

  • Thread starter Thread starter ianripping
  • Start date Start date
I

ianripping

I want to create a formula which loks for two values in two colums.

I think it it should look like this:-
=SUMPRODUCT((A1:A3="DEC")*(B1:B3="A"))

Eg

Coulmn A

29TH NOV 03
1ST DEC 03
2ND DEC 03

COLUMN B

A
B
A


I want the formula to look for any cells in Coulmn A which have DEC i
bu must have a corresponding A in column B. So the answer in thi
instance would be 1.

Any clues
 
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

ianripping said:
I want to create a formula which loks for two values in two colums.

I think it it should look like this:-
=SUMPRODUCT((A1:A3="DEC")*(B1:B3="A"))

Eg

Coulmn A

29TH NOV 03
1ST DEC 03
2ND DEC 03

COLUMN B

A
B
A


I want the formula to look for any cells in Coulmn A which have DEC in
bu must have a corresponding A in column B. So the answer in this
instance would be 1.

Any clues?


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
creating financial statements
 
Ian,,

=SUMPRODUCT((TEXT(A1:A3,"mmm")="DEC")*(B1:B3="A"))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

ianripping said:
I want to create a formula which loks for two values in two colums.

I think it it should look like this:-
=SUMPRODUCT((A1:A3="DEC")*(B1:B3="A"))

Eg

Coulmn A

29TH NOV 03
1ST DEC 03
2ND DEC 03

COLUMN B

A
B
A


I want the formula to look for any cells in Coulmn A which have DEC in
bu must have a corresponding A in column B. So the answer in this
instance would be 1.

Any clues?


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
creating financial statements
 
Or if the string in A is text and not dates

=SUMPRODUCT(--(ISNUMBER(FIND("DEC",A1:A3))),--(B1:B3="A"))
 
I think the ordinals are going to keep this from working:

A1: 29TH NOV 03
C1: =TEXT(A1,"mmm") ===> 29TH NOV 03

Perhaps something like:

=SUMPRODUCT(1-(ISERR(SEARCH("DEC",A1:A3))),--(B1:B3="A"))
 
Back
Top