Sum Formulae with more than 1 Criteria
 

Hi All,
Can you please advise what is the best formulae to use to pick up the
sum of data on the basis of 2 or more criteria.

Example of data

Month Program Number Title Sales £
APR ABAE973H CBSxxx 1
APR ABAN122A Other 2
APR ABAP056H xxxCBS 3
APR ABAP057B CBS 4
APR ABBP054E CBS 5
APR ABBQ594F Other 6
APR ABCA585K Index 7
APR ABCA612R CBS 8
APR ABCA813N CBS 9
MAY ABDA460R CBSdel 14
MAY ABDA461K CBS 15
MAY ABDA463Y CBS 16
MAY ABDB064R CBS 17
MAY ABDB096D CBS 18
JUNE ABDB107D CBS 19
JUNE ABDB220D CBS 20
JUNE ABDB262F CBS 21
JUNE ABDB373S CBS 22
JUNE ABDB665T CBS 23
JUNE ABDB778T Blacklight 24
JUNE ABDB843B Nugus 25
JUNE ABDB991W CBS 26
JUNE ABDC058F CBS 27

Require a formulae to pick up all the CBS sales in April.
Answer should be £30

Many Thanks
TW

Sum Formulae with more than 1 Criteria
 

Assuming your columns are A - D and there are 50 rows of data (you ca
adjust this in the formula just keep them all the same length) you ca
use this formula

=SUMPRODUCT((A1:A50="APR")*(C1:C50="CBS")*(D1:D50) )

Wrote:
Hi All,
Can you please advise what is the best formulae to use to pick up the
sum of data on the basis of 2 or more criteria.

Example of data

Month Program Number Title Sales =A3
APR ABAE973H CBSxxx 1
APR ABAN122A Other 2
APR ABAP056H xxxCBS 3
APR ABAP057B CBS 4
APR ABBP054E CBS 5
APR ABBQ594F Other 6
APR ABCA585K Index 7
APR ABCA612R CBS 8
APR ABCA813N CBS 9
MAY ABDA460R CBSdel 14
MAY ABDA461K CBS 15
MAY ABDA463Y CBS 16
MAY ABDB064R CBS 17
MAY ABDB096D CBS 18
JUNE ABDB107D CBS 19
JUNE ABDB220D CBS 20
JUNE ABDB262F CBS 21
JUNE ABDB373S CBS 22
JUNE ABDB665T CBS 23
JUNE ABDB778T Blacklight 24
JUNE ABDB843B Nugus 25
JUNE ABDB991W CBS 26
JUNE ABDC058F CBS 27

Require a formulae to pick up all the CBS sales in April.
Answer should be =A330

Many Thanks
T

--
Excelenato

-----------------------------------------------------------------------
Excelenator's Profile: http://www.excelforum.com/member.php...fo&userid=3676
View this thread: http://www.excelforum.com/showthread.php?threadid=56789

Sum Formulae with more than 1 Criteria
 

that wouldn't pick up the CBSxxx or xxxCBS lines. A modification would be:

=SUMPRODUCT(--(A1:A50="APR"),--(ISNUMBER(SEARCH("cbs",C1:C50))),D1:D50)

This was tested with your data an produced 30.

If you need a case sensitive test for CBS to differentiate CBS from cbs, then

=SUMPRODUCT(--(A1:A50="APR"),--(ISNUMBER(FIND("CBS",C1:C50))),D1:D50)

--
Regards,
Tom Ogilvy






"Excelenator" wrote:


Assuming your columns are A - D and there are 50 rows of data (you can
adjust this in the formula just keep them all the same length) you can
use this formula

=SUMPRODUCT((A1:A50="APR")*(C1:C50="CBS")*(D1:D50) )

Wrote:
Hi All,
Can you please advise what is the best formulae to use to pick up the
sum of data on the basis of 2 or more criteria.

Example of data

Month Program Number Title Sales =A3
APR ABAE973H CBSxxx 1
APR ABAN122A Other 2
APR ABAP056H xxxCBS 3
APR ABAP057B CBS 4
APR ABBP054E CBS 5
APR ABBQ594F Other 6
APR ABCA585K Index 7
APR ABCA612R CBS 8
APR ABCA813N CBS 9
MAY ABDA460R CBSdel 14
MAY ABDA461K CBS 15
MAY ABDA463Y CBS 16
MAY ABDB064R CBS 17
MAY ABDB096D CBS 18
JUNE ABDB107D CBS 19
JUNE ABDB220D CBS 20
JUNE ABDB262F CBS 21
JUNE ABDB373S CBS 22
JUNE ABDB665T CBS 23
JUNE ABDB778T Blacklight 24
JUNE ABDB843B Nugus 25
JUNE ABDB991W CBS 26
JUNE ABDC058F CBS 27

Require a formulae to pick up all the CBS sales in April.
Answer should be =A330

Many Thanks
TW


--
Excelenator


------------------------------------------------------------------------
Excelenator's Profile: http://www.excelforum.com/member.php...o&userid=36768
View this thread: http://www.excelforum.com/showthread...hreadid=567893

Sum Formulae with more than 1 Criteria
 

Hi Tom,
You are spot on - this is exactly what I needed.

Just a quick question on this if you have time.
Why the two negative signs? It also seem to get the same result with
just one negative sign?

Many thanks again
TW


Tom Ogilvy wrote:

that wouldn't pick up the CBSxxx or xxxCBS lines. A modification would be:

=SUMPRODUCT(--(A1:A50="APR"),--(ISNUMBER(SEARCH("cbs",C1:C50))),D1:D50)

This was tested with your data an produced 30.

If you need a case sensitive test for CBS to differentiate CBS from cbs, then

=SUMPRODUCT(--(A1:A50="APR"),--(ISNUMBER(FIND("CBS",C1:C50))),D1:D50)

--
Regards,
Tom Ogilvy






"Excelenator" wrote:


Assuming your columns are A - D and there are 50 rows of data (you can
adjust this in the formula just keep them all the same length) you can
use this formula

=SUMPRODUCT((A1:A50="APR")*(C1:C50="CBS")*(D1:D50) )

Wrote:
Hi All,
Can you please advise what is the best formulae to use to pick up the
sum of data on the basis of 2 or more criteria.

Example of data

Month Program Number Title Sales =A3
APR ABAE973H CBSxxx 1
APR ABAN122A Other 2
APR ABAP056H xxxCBS 3
APR ABAP057B CBS 4
APR ABBP054E CBS 5
APR ABBQ594F Other 6
APR ABCA585K Index 7
APR ABCA612R CBS 8
APR ABCA813N CBS 9
MAY ABDA460R CBSdel 14
MAY ABDA461K CBS 15
MAY ABDA463Y CBS 16
MAY ABDB064R CBS 17
MAY ABDB096D CBS 18
JUNE ABDB107D CBS 19
JUNE ABDB220D CBS 20
JUNE ABDB262F CBS 21
JUNE ABDB373S CBS 22
JUNE ABDB665T CBS 23
JUNE ABDB778T Blacklight 24
JUNE ABDB843B Nugus 25
JUNE ABDB991W CBS 26
JUNE ABDC058F CBS 27

Require a formulae to pick up all the CBS sales in April.
Answer should be =A330

Many Thanks
TW


--
Excelenator


------------------------------------------------------------------------
Excelenator's Profile: http://www.excelforum.com/member.php...o&userid=36768
View this thread: http://www.excelforum.com/showthread...hreadid=567893