Summing Data Based on Criteria and Collectively [SOLVED]

[gmelchor]

Hello all,


I am working on making the calculations section of my dashboard but have run into a problem. I am showing the number of cases completed and the average turnaround time based on three separate criteria that I want to display: Month, Employee, Priority level.


I want to be able to display the values based on what the user has selected. I have used SUMIFS to successfully show the sum if individual criteria are selected, but I do not know how to include an "All" selection. For example: instead of showing the breakdown of cases for January under an Urgent priority I want to show a breakdown of ALL cases in 2012 under ALL prorities.

 

[Luke M]

I'd write a formula like:

=IF(A2="All",SUMIF(MonthRange,"January",Data),SUMIFS(MonthRange,January,PriorityRange,"Urgent,Data))

 

[gmelchor]

I guess I should mention that there isn't an "All" value in my data, only the months, priority, etc.


I have considered including an "All" for each possible combination but considering I have 8,000 rows the file would become enormous.


Is there a way to tell Excel that if I select "All" to group and sum the entirety of that particular criteria and if I don't select "All" then only sum the specified criteria?

 

[Luke M]

This might be easier if you posted the actual formula you are using, so we can use that as a reference point. There are certainly ways to word the formula to be "all-inclusive", but it will depend on the formula structure. One other way would be control the criteria cell.


Let's say your formula currently looks like this:

=SUMIF(A:A,C2,B:B)

Where C2 is the user input. If, we put a formula in C3 like this:

=IF(C2="All","*",C2)

we can then change the formula to be:

=SUMIF(A:A,C3,B:B)

and now when the user chooses "all", the formula will include all data points. This methodology could obviously be expanded to the SUMIFS function as well.

 

[gmelchor]

Sorry to revisit this old post, but I thought that if I provided a working example (as suggested by Luke) that it would be easier to get some help. I have exhausted searching all over the web and still can't figure this out.


Here is the link to the file with instructions explained: https://www.dropbox.com/s/397wg7ttzaqau9h/criteriasumming.xlsx


I would be amazed and in debt to anyone who can devise a solution!

 

[gmelchor]

Crap I forgot a major component of my question. It is important that I enable my end-user to sum based on 'All' of any one of the criteria. For example, how many 'Dogs' and 'Cats' in 2012 were 'Adopted'. Also, how many 'Dogs' and 'Cats' were adopted in both '2012' and '2013'.


I'm sorry I forgot to include that condition. If it would help for me to reformat my uploaded document then please let me know and I will.

 

[Smallman]

Hi gmelchor


This will give you a result based on one part of each criteria.


=SUMPRODUCT((B3:B16=H7)*(C3:C16=I7)*(D3:D16=J7)*(E3:E16=K7))


You could also use Countifs with the later versions of XL and based on your last post you will need it. You will have to change the set up of your file to get what you need. Firstly create a third dropdown criteria in H7


>=2012


Now in I7-K7 create a criteria in the drop down for the wildcard character;


*


Now you should be able to count every permutation with the following;


=COUNTIFS($B$3:$B$16,H8,$C$3:$C$16,I8,$D$3:$D$16,J8,$E$3:$E$16,K8)


Of course the above assumes you are using a recent version of XL. Hope this helps.


Take care


Smallman

 

[NARAYANK991]

Hi ,


Can you check whether the file here gives correct results ?


https://www.dropbox.com/s/b3jhenyt10sfmoc/criteriasumming.xlsx


Narayan

 

[gmelchor]

Narayank, your solution definitely worked, but I think Smallman's solution suits my problem better. I need to use it with multiple (6+) criteria in the future and Smallman's solution is effective and simple. I never knew a wildcard would return all results!


Thanks so much for your help everyone!

 

[Smallman]

Gmelchor


Glad we could assist you and you got the result in the end.


Take care


Smallman