Multple criteria dilemma

[Grant Reid]

Hi

I really hope someone out there can help with this problem. I need to
calculate a total for rows that meet one or more criteria, however what
complicates matters for me is that the number of criteria changes, sometimes
it will be one, sometimes four, sometimes three etc etc. I need to
accomplish this without resorting to VBA.

The worksheet that contains the criteria has the following format, with the
criteria in row 2

A B C
D E
1 Year Account Manager Client
Product Revenue Type
2 2005 Brendan Gannon FNB
Businees Objects Product

At times I will want a total for rows that meet less than the five criteria
for example

A B C
D E
1 Year Account Manager Client
Product Revenue Type
2 Brendan Gannon FNB
Product

Sometimes there will be only one criteria, for example

A B C
D E
1 Year Account Manager Client
Product Revenue Type
2
Businees Objects


The data portion resides below the criteria range in row 5 through to 100
and the column that needs to be summed is Column F, row 5 through to 100

Any help in this regard will be much appreciated.

Kind Regards - Grant

 

[Roger Govier]

Hi Grant

I'm not sure I fully understand what you are looking for, but maybe in
F2 you could enter
=COUNTA(A2:E2)
and copy down.
This will give a count of the number of columns with data entered.
If you then want to know how many rows have 3 items entered
=COUNTIF(F2:F100,3)

 

[Grant Reid]

Hi Roger

Many thanks for your response. After reading my original question, I realised I had over complicated matters. What I'm actually
trying to accomplish is this..... I have data in A5:F100, I need to sum the numeric data in F5:F100 that meets criteria I have
A2:E2. Sometimes I'll need sum the data that meets all 5 criteria, sometimes I'll need to sum data that meets just one, sometimes
three etc etc

So sometimes it will look like this
A B C D E
Row 1 Year Acc Clnt Prod Rev
Row 2 xxxx xxxx xxxx xxxx xxxx

sometimes it will look like this
A B C D E
Row 1 Year Acc Clnt Prod Rev
Row 2 xxxx xxxx xxxx

sometimes like this

A B C D E
Row 1 Year Acc Clnt Prod Rev
Row 2 xxxx

etc etc. So my problem is how to sum the data in F5:F100 when the number and position of criteria vary.

Kind Regards - Grant

 

[Roger Govier]

Hi Grant

The take a look at the Sumproduct function
=SUMPRODUCT(--($A$2:$A$100="criteria1"),--($B$2:$B$100="criteria2"),$F2:$F100)

Keeping F2:F100 constant, insert or delete as
ny --($XX2:$XX100="criteria") as you wish, (where XX equals your
column letter)

Change the range to suit your needs, but do ensure that each range used
is of equal length.

 

[Debra Dalgleish]

Another option is to create a pivot table from the data, and add fields
to the page area. Use these fields to filter the results, or to show
results for all records. There are examples and links here:

http://www.contextures.com/xlPivot01.html

 

[Guest]

The DSUM function is good for rapidly summing large sets of data with
multiple criteria. The way you described your criteria (with headings) in
A1:E2 is exactly what DSUM requires, and it's fine if the number of criteria
(A2:E2 cells with values) varies from one time to the next.

Check the Excel help for DSUM.

Hope this helps,

Hutch

 

[Grant Reid]

Hi

Many Thanks to all who responded. Your help is much appreciated. Unfortunately the goal posts have been shifted and I now have to
rethink my approach to this problem. Once again, any help would be much appreciated. I'm now required to embed this spreadsheet into
another product (Crystal Xcelsius - a dashboarding product that sits on top of Excel) and therefore things become much more rigid. I
cannot use VBA in the spreadsheet, I cannot use any the menu options neither can I make use of functionality such as pivot tables. I
am restricted to entering/deleting/modifying data in 5 cells and producing 12 different results by means of functions.

I now have my data in A5:G100 and need to sum the numeric data in G5:G100 that meets criteria I have A2:E2. Sometimes I'll need sum
the data that meets all 5 criteria, sometimes I'll need to sum data that meets just one, sometimes three etc etc. My data now has an
additional column, this contains the month. The layout is like this (row 5 through row 100);

A B C D E F G
Row 5 Year Acc Clnt Prod Rev Month Amount

My critera will sometimes look like this
A B C D E
Row 1 Year Acc Clnt Prod Rev
Row 2 xxxx xxxx xxxx xxxx xxxx

sometimes it will look like this
A B C D E
Row 1 Year Acc Clnt Prod Rev
Row 2 xxxx xxxx xxxx

sometimes like this

A B C D E
Row 1 Year Acc Clnt Prod Rev
Row 2 xxxx

etc etc.

So I still have the same problem, how to sum the data in F5:F100 when the number and position of criteria vary and if I had to
produce this result in one cell, DSUM would be the ideal solution. But now I have to produce a result for each month of the year,
based on the same criteria entered in A1:E2. I somehow need to append this additional "Month" criteria to the original criteria and
produce 12 seperate results in 12 different cells.

I've attempted to create 12 different criteria areas, one for each month, adding the month to my criteria for each and "Paste
Linking" to my original criteria in A1:E2. This works fine as long as all five criteria are entered in A1:E2. As soon as I remove
one of the criteria, the corresponding "Paste Linked" criteria shows 0 and the expected results are not returned.

Once again, any help would be much appreciated.

Kind Regards - Grant

 

[Roger Govier]

Hi Grant

Sumproduct should still provide your solution.
Take a lock at Bob Phillips site for more help on this function
http://xldynamic.com/source/xld.SUMPRODUCT.html

 

[Grant Reid]

Hi

I am still bumping my head with this one. I've had a look at http://xldynamic.com/source/xld.SUMPRODUCT.html as Roger suggested. It
certainly seems as if Sumproduct could be the answer, but for the life of me I can't figure how to apply it to what I'm trying to
accomplish.

Just to summarise again what I'm trying accomplish. I have my data residing in A6:G100
A B C D E F G
Row 5 Year Acc Clnt Prod Rev Month Amount

I need to sum the numeric data in G5:G100 that meets criteria I have in A2:E2
A B C D E
Row 1 Year Acc Clnt Prod Rev
Row 2 xxxx xxxx xxxx xxxx xxxx

but I need to do this 12 times - one result for every month in 12 different cells - assume results go H1:H12. The data in the month
column, F6:F100 is numeric, obviously ranging 1 through to 12.

To complicate matters even further, sometimes I will have number of criteria permutations in A2:E2. Sometimes I will have all five
criteria, sometimes three, sometimes four, somtimes two, sometimes one and even on occasion, none. Below is an example of where I
have three criteria
A B C D E
Row 1 Year Acc Clnt Prod Rev
Row 2 xxxx xxxx xxxx

and one criteria
A B C D E
Row 1 Year Acc Clnt Prod Rev
Row 2 xxxx

etc etc

All of this has to be accomplished without resorting to array formulae, VBA or functionality such as pivot tables.

Any further help will be much appreciated

Kind Regards - Grant

 

[Roger Govier]

Hi Grant

Compared with the original posting,column G now appears to contain your
values to be summed

=SUMPRODUCT(--($A$2:$A$100="criteria1"),
--($B$2:$B$100="criteria2"),
--($C$":$C$100="criteria3),
--($D$2:$D$100="criteria4"),
--($E$2:$E$100="criteria5"),
--($F$2:$F$100=1),
$G2:$G100)

This would test the case where Month is 1 (F2:F100=1), and that there
were criteria in the other 5 columns.
Omit the section relating to any one of the columns to exclude that from
the result.
If you are going to do this for each month, with 1 in H1, 2 in H2 etc,
change to
--($f$2:$F$100=H1) and copy down.
To deal with anything from 1 to 5 criteria, you would need to copy
across, with each formula containing an additional criteria, so you
would have a matrix of 60 cells with the differing results.