Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formulae: Paste value formulae after doing an average operation | Excel Discussion (Misc queries) | |||
Formulae to use for more that 2 conditions criteria | Excel Discussion (Misc queries) | |||
Searching TEXT in formulae, rather than results of formulae | Excel Worksheet Functions | |||
Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions |