• 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.

Formula challenge - sum criteria

blcexcel

Member
I need help creating a formula that will take into account criteria on a different tab. I have an example on the attached file.

Any help would be appreciated.

Thanks in advance!!!
 

Attachments

Chihiro

Excel Ninja
It's not clear what end result that you are after. I'd recommend uploading sample with some examples of expected output (manually calculated).
 

Chihiro

Excel Ninja
I'd recommend restructuring your data to simplify things.

I.E. Flatten your data from cross-tab structure.

Method will depend on your Excel Version and how backward compatible it must be.
 

blcexcel

Member
I'm running Office 365. This would need to be compatible for Excel 2017.

I'm unable to change the data format due to the large amount of data I have and system constraints.

I also want to stay away from array formulas because this spreadsheet will be used for a large user population and they would not know how to deal with array formulas.

I need to avoid volatile formulas because this will be a large file and formulas such as indirect would result in calculation times that would make the spreadsheet unusable.

I worked on a solution for this for half a day solid. I can get the formula to sum the data for the first month with the "x" but I don't know how to make it work for the range of months.
 

Chihiro

Excel Ninja
Without use of array formula, and with current set up... It will be rather complex formula at any rate.

You'll likely not have viable solution without data structure change. Though other's more versed in formula solution may be able to help.

You can always change data structure before publishing for general use. (I.E. Use Get & Transform to restructure your data without altering the original, then break connection if compatibility is an issue).

Using cross-tab as starting point of data analysis is very common mistake, and should be avoided. It creates more headache than the little benefit it affords (human readability). Cross-tab should be the end-result and not the starting point.
 

blcexcel

Member
I could probably get away with using an array formula. It is not possible to export the data from my system without using cross-tab format. I need to use this particular system for this solution.
 

p45cal

Well-Known Member
Your months at the top of the tables in tab1 and tab2 are strings (not real dates) but it would be better if they were real dates so that there'd be no question of muddling months from different years. I did this in the attached file ending 'a'.
You can get away with only changing the headers of the table in tab1 so that they match the headers of the table in tab2. I've done this in the attached file ending 'b'.

It's the same array-entered formula in both workbooks, in cell D11:
Code:
=SUM(IF(ISNUMBER(MATCH('tab2'!$C$3:$N$3,IF(ISNUMBER(MATCH(IF(INDEX('tab1'!$B$7:$M$11,MATCH(B11,'tab1'!$A$7:$A$11,0),0)="X",'tab1'!$B$6:$M$6),'tab1'!$B$6:$M$6,0)),'tab1'!$B$6:$M$6),0)),INDEX('tab2'!$C$4:$N$28,MATCH(B11 & C11,'tab2'!$A$4:$A$28 & 'tab2'!$B$4:$B$28,0),0)))
and copy down.

It could be more elegant, but it's as the first time I got the right results. Too lazy.
 

Attachments

Last edited:

bosco_yip

Excel Ninja
Or, try this non-array formula

In D11, enter formula and copied down :

=SUMPRODUCT(('tab2'!$A$4:$A$28=B11)*('tab2'!$B$4:$B$28=C11)*INDEX('tab2'!$C$4:$N$4,MATCH('tab1'!B$6,'tab2'!$C$3:$N$3,0)):'tab2'!$Q$28*(INDEX('tab1'!$B$7:$M$11,MATCH(B11,'tab1'!$A$7:$A$11,0),0)="X"))

Regards
Bosco
 

Attachments

blcexcel

Member
Question on the sumproduct formula. You have a reference to cell Q28 but there is nothing in cell Q28. Should that be a reference to a different cell? Thanks in advance!
 

blcexcel

Member
If I change the Q28 reference to any other cell the formula doesn't work. This is problematic for converting the formula from this example to my actual spreadsheet. Maybe if you can explain what that reference is doing in the formula. Thanks in advance!
 

bosco_yip

Excel Ninja
Question on the sumproduct formula. You have a reference to cell Q28 but there is nothing in cell Q28. Should that be a reference to a different cell? Thanks in advance!
SUMPRODUCT ( "tab2_array"*"tab1_array") required all array should be in same dimension, but your "tab1" table and "tab2" table appear in different width.

In order to combine 2 tables for calculation in SUMPRODUCT(), I use "tab1" table column width 12 for 2 tables array, that is the reason to use Q28.

So,

For easy understanding , it can also use OFFSET() instead of INDEX() and D11 become :

=SUMPRODUCT(('tab2'!$A$4:$A$28=B11)*('tab2'!$B$4:$B$28=C11)*OFFSET('tab2'!$B$4,0,MATCH('tab1'!B$6,'tab2'!$C$3:$N$3,0),25,12)*(INDEX('tab1'!$B$7:$M$11,MATCH(B11,'tab1'!$A$7:$A$11,0),0)="X"))

The result will be same as post #.10 formula

Regards
Bosco
 
Last edited:

Peter Bartholomew

Well-Known Member
I tackled this twice. Once for a dynamic array version of Excel and the other for traditional Excel. What I prefer in a solution is to write the formula once and have it spill to populate the table but two characteristics of the problem prevented that.
1. Aggregations are performed column by column so, unless one is planning to use MMULT, the month needs to be determined by a relative reference and the corresponding columns extracted from the 2D array.
2. Although SUMIFS accepts the 'climate' letters as an array, it expects range references for its value and criteria parameters. Given that we need to perform calculations on one or other, the climate is also determined by a relative reference allowing the use of arrays to filter the climate letter.

62071
As a further observation, if one is planning to use arrays, rather than tables/lists, the associated indices must be absolutely regular and consistent between arrays/tables. The Tab1 table had and extra Sub=127 and some F and G rows were missing from the Tab2 table.
 

Attachments

blcexcel

Member
Using some tips from everyone I was able to create a formula that is working. There is one improvement I would like to make. Currently the formula will work if month numbers are entered in row 5. This is the row users will enter data. They will be entering "X"s. If I don't get a a solution that will work for "X"s I will have to create formulas to the side of the spreadsheet to convert the "X"s to numbers and there are actually 48 columns where they can enter values. Thus, I would prefer the formula to be able to work if "X"s are entered instead of month numbers if anyone has input on that. Thanks in advance!!!
 

Attachments

Top