Sum Formulae with more than 1 Criteria

[thewritings@googlemail.com]

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]

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

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

[Tom Ogilvy]

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))
>
> thewritings@googlemail.com 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
>
>

[thewritings@googlemail.com]

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))
> >
> > thewritings@googlemail.com 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
> >
> >