Sum Formulae with more than 1 Criteria

[thewritings]

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

 

[Excelenator]

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")*(D150))

 

[Guest]

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

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

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))),D150)

--
Regards,
Tom Ogilvy

 

[thewritings]

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

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

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

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))),D150)

--
Regards,
Tom Ogilvy