• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

SUMPRODUCT with multiple criteria & 2 embedded IF statements

S

ScottJB

New Member
Hi all,

Looking for some input from a Excel guru much smarter than i. I'm wrestling with a SUMPRODUCT formula to count matching entries on multiple criteria. I'm still on Excel 2003 so can't use COUNTIFS etc.

To give you an idea what i'm trying to achieve. I started with this...

=SUMPRODUCT(--('Injury Data_Core'!$X$4:$X$1000=$B$1),--('Injury Data_Core'!$B$4:$B$1000=$B$2),--('Injury Data_Core'!$N$4:$N$1000=$B$3),--('Injury Data_Core'!$K$4:$K$1000=$A39),--('Injury Data_Core'!$F$4:$F$1000=B$38)))

Essentially checking 5 columns of data within a larger 15 column data table for multiple matching criteria. This works fine. B1,B2,B3 are cells containing dynamic criteria that is controlled by front end combo boxes. A39 & B38 are fixed criteria. However, i need alternative scenarios for B2 & B3 where if the combo box = All (not actually a match in the data table). It eliminates that part of the SUMPRODUCT.

I've managed to write the below which when the end user selects All in the 3rd combo box the SUMPRODUCT drops the (,--('Injury Data_Core'!$N$4:$N$1000=$B$3) bit for the TRUE statement. This is working perfect too.

=IF($B$3="All",SUMPRODUCT(--('Injury Data_Core'!$X$4:$X$1000=$B$1),--('Injury Data_Core'!$B$4:$B$1000=$B$2),--('Injury Data_Core'!$K$4:$K$1000=$A39),--('Injury Data_Core'!$F$4:$F$1000=B$38)),SUMPRODUCT(--('Injury Data_Core'!$X$4:$X$1000=$B$1),--('Injury Data_Core'!$B$4:$B$1000=$B$2),--('Injury Data_Core'!$N$4:$N$1000=$B$3),--('Injury Data_Core'!$K$4:$K$1000=$A39),--('Injury Data_Core'!$F$4:$F$1000=B$38)))

However, i can't successfully adapt the above to embed a 2nd IF for when B2 = "All" aswell.

Can someone take a look and assist. Appreciate if can be a bit difficult to visualise without a sample file but would take me a while to remove the sensitive information.

I'm sure there is a more elegant way of writing this and possibly losing the IF statement altogether but i'm a bit stumped so far.

Thanks in advance

Scott
 
Hi Scott,

What is there in those combo boxes apart from All, are they numbers or TEXT?

And can you upload a sample file with say 100 lines of various data.

Regards,
 
Hi Somendra

Thanks for looking.

All 3 front end combo boxes are text options for the user to select, the combo boxes are linked to cells A1,A2,A3. Adjacent cells B1,B2,B3 contain VLOOKUP formula to lookup the relevant criteria to match based on combo box position. e.g. position 1 = All, position 2 = Department 1 etc. Data table is also text.
 
@ScottJB

One more question if the text in combo box are a part of full text in column or they are the exact words in column where they are criteria.

I mean is it like in column of criteria in your main data Say words are department 1, department 2 and so on and in combobox there is is department only?

Is it like this ?

Regards,
 
Lets say :

Combo Box 1 (cell linked to A1, criteria in B1) = 2014, 2015, 2016, 2017 Combo Box 2 (cell linked to A2, criteria in B2) = All Areas (not actually a match in the data), Area 1, Area 2, Area 3, etc
Combo Box 3 (cell linked to A3, criteria in B3) = All Departments (not actually a match in the data), Department 1, Department 2, etc

In my mind, i just need to be able to ignore certain parts of the SUMPRODUCT formula when the combo boxes = All Area or All Departments. I think thats what i achieved in the below formula, but i can't work out how to add the additional IF for when Combo Box 2 = All Areas.

=IF($B$3="All Departments",SUMPRODUCT(--('Injury Data_Core'!$X$4:$X$1000=$B$1),--('Injury Data_Core'!$B$4:$B$1000=$B$2),--('Injury Data_Core'!$K$4:$K$1000=$A39),--('Injury Data_Core'!$F$4:$F$1000=B$38)),SUMPRODUCT(--('Injury Data_Core'!$X$4:$X$1000=$B$1),--('Injury Data_Core'!$B$4:$B$1000=$B$2),--('Injury Data_Core'!$N$4:$N$1000=$B$3),--('Injury Data_Core'!$K$4:$K$1000=$A39),--('Injury Data_Core'!$F$4:$F$1000=B$38)))
 
Area 1, Area 2, Area 3 would all be direct matches in the data table column B4:B1000. All Areas would not be a match.

Department 1, Department 2 etc would all be direct in the data column column N4:N1000. All Departments would not be a match.
 
Can't you just use a PivotTable for this?

In fact, let me rephrase that:

I'm certain you can use a PivotTable for this. I'm also certain that a PivotTable is probably a much more efficient and simpler approach that pointing a complicated SumProduct with multiple conditions over a thousand row array.
 
Last edited:
Hi Jeff

Thanks for taking a look at my thread.

As you rightly say a pivot table would be a significantly easier approach to a formula based solution and would give me all the flexibility i want to slice the data.

However, my organisation is stuck on 2003 for now and pivot charts as standard look horrendous. I can format them to look better but once you refresh the data, the formatting is lost (a known issue i believe?). I think i did read about a short VBA code to reapply the formatting which might make sense as a process? (Append the new data>Single button to refresh all pivots>single button to reapply desired formatting).

Ultimately, i want a slick, professional looking front end. Banners, combo boxes, nicely formatted charts/tables. And i'm of the opinion (maybe falsely) i couldn't make it look as nice as i want using a pivot solution.
 
Hi Scott ,

As Jeff has posted , it is possible a Pivot Table would do the job easily.

However , I would like to point out a different method.

If you look at what the SUMPRODUCT is doing , it is basically forming an array of values ; all that the conditionality checks will do is output an array of TRUE / FALSE values , which when multiplied together result in 1s and 0s.

Multiplying all of the conditionality checks together results in an AND of all the conditions ; when this final array of 1s and 0s is multiplied with the values array , you get a value where there is a 1 and 0 otherwise.

Thus , when you have a conditionality check within a check , all you need to do is phrase the formula slightly differently.

Suppose we have just a single check for departments , and the SUMPRODUCT formula is a simple one , as follows :

=SUMPRODUCT((Departments=Selected_Department)*(Values))

Now , if you include a new selection such as All Departments , just change the above formula to :

=SUMPRODUCT((Departments=IF(Selected_Department="All Departments",Departments,Selected_Department))*(Values))

where the added part is highlighted. You need to enter this formula as an array formula , using CTRL SHIFT ENTER.

What this does is that if All Departments is selected , the resulting array is an array of 1s , thus effectively excluding the check from the SUMPRODUCT function.

Narayan
 
Thanks also for taking a look Narayan. I recall your name as you sucessfully helped me out last time i posted a thread probably over a year ago now.

I'll try to digest the above and apply to my scenario. Of course if you are able to take a look at applying it to my sample file in post 8 that would be fantastic.
 
@ScottJB

Well I also personally feel this should be much easier than PT, but see the attached file, with only SUMPRODUCT function, Just a little addition. Check for result as I don't got time to check entire results.

Regards,
 

Attachments

  • For Upload_WIP.xls
    213 KB · Views: 14
Hi Scott ,

In cell B22 , try the following formula , entered as an array formula , using CTRL SHIFT ENTER :

=SUMPRODUCT(('Injury Data_Core'!$X$4:$X$933=$B$1)*('Injury Data_Core'!$B$4:$B$933=$B$2)*('Injury Data_Core'!$N$4:$N$933=IF($B$3="All Departments",'Injury Data_Core'!$N$4:$N$933,$B$3))*('Injury Data_Core'!$K$4:$K$933=$A22)*('Injury Data_Core'!$F$4:$F$933=B$21))

Including the check for All Areas can be done similarly.

Narayan
 
You can also pull the data into a hidden PivotTable, then reference that PivotTable dynamically using the GETPIVOTDATA function. That gives you the best of both worlds.
 
...or another option is to have some very basic code that filters your hidden PivotTable based on data validation choices, and then your Calculation sheet just points at the PivotTable.

I'll put together a sample file, as this is something I intend to cover in my upcoming book Excel for Superheroes and Evil Geniuses
 
Scott: Would you like users to be able to select multiple Departments or Years if they so wish? e.g. "Give me Departments 3, 5, and 7 for 2013 and 2014"
 
Check out the sample file. No resource-hogging SUMPRODUCTS. No VBA. No Data Validation lists to maintain. No drama for you (or someone that replaces you) to understand in 1 year's time.

Just a PivotTable with everything hidden but the PageFields (meaning users can select whatever they want - or even groups of things), some formulas that point at the hidden Data area of the PivotTable, and some custom number formatting to hide zeros.

It's like the old saying: Things should be as simple as possible, and no simpler.
 

Attachments

  • PivotTable as Staging Area_20141031.xls
    217.5 KB · Views: 22
Thanks, Somendra...I'll need it. I still have masses of it to write. Part of the reason I hang out here is because it helps me come up with things to write about. But mostly, I'm just flat-out procrastinating!
 
You must log in or register to reply here.
Back
Top