1. ### Welcome to Chandoo.org Forums. Short message for you

Hi Guest,

Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

Yours,
Chandoo
2. 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...

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

# SUMPRODUCT with Criteria

Discussion in 'Ask an Excel Question' started by PaulFogel123, Jan 12, 2017.

1. ### PaulFogel123Member

Messages:
129
Hello,

Another challenge formula! The attached file has two tables. I created a weighted average using the SUMPRODUCT formula with criteria added, like so:

(SUMPRODUCT((Analysis!\$K\$4:\$K\$31="Medical")*(Analysis!L\$4:L\$31)*(Analysis!\$C\$4:\$C\$31="Medical")*(Analysis!D\$4:D\$31)))/L35
It was two tables that I converted to ranges to make it easier to see in this sample. It works great. But here's the problem: These tables must remain separate, and each column is sortable. When the sort order is not the same in each table, the SUMPRODUCT formula doesn't work as intended--it simply multiplies the first instance in one table by the first instance in the other table.

Is there an additional criterion to add to fix this, or is there a better way?

Paul

File size:
544.7 KB
Views:
5
2. ### PaulFogel123Member

Messages:
129
One solution might be to insert columns L-P into the first table so that all the data would be in one place. SUMPRODUCT would work fine then. I could hide the duplicate columns if I didn't want to display them. But I'm hoping there's a less cumbersome solution.
3. ### NARAYANK991Excel Ninja

Messages:
15,625
Hi Paul ,

I doubt that what you want done can be done.

Let us assume that there is only one value which is Medical , both in column C and in column K.

When the tables are sorted differently , it can so happen that in column C , this value occurs in cell C16 , while in column K , it may occur in K23.

The result of this section of the formula :

(Analysis!\$K\$4:\$K\$31="Medical")

will have all zeros , except for 1 due to the occurrence in K23.

The result of this section of the formula :

(Analysis!\$C\$4:\$C\$31="Medical")

will have all zeros , except for 1 due to the occurrence in C16.

Naturally the 1 in the two arrays will occur at different positions within the array ; multiplying the two arrays will result in zeros in every position , and thus will not give the intended result.

More occurrences of Medical within columns C and K will not change the problem.

Narayan
4. ### HuiExcel NinjaStaff Member

Messages:
10,695
Is there any way that you can simply use Column B & C and not use a Column J & K as they are direct copies anyway?
PaulFogel123 likes this.
5. ### PaulFogel123Member

Messages:
129
I sent an extract of two tables to Chandoo. Yes, I could combine them into one table--technically, not a problem--but the report formats wouldn't be aesthetically pleasing, which is important here, considering the audience.

I think the easiest solution will be to append the data from the second table into the first table so that positioning for SUMPRODUCT purposes won't be an issue. The first table will contain within it all the data needed to perform the calculations correctly without having to refer to other tables. And then just hide the duplicate columns so no one is the wiser. It's a way of connecting the two tables without tricky and cumbersome formulas.
6. ### PaulFogel123Member

Messages:
129
You are exactly right. I think the solution I proposed to Hui above is the simplest way to fix this limitation. I thought there might be a formula to do this, but it seems there isn't one suited to this purpose. But a workaround solution is still a solution.
Thanks for your insight here.
Paul
7. ### HuiExcel NinjaStaff Member

Messages:
10,695
I'd suggest having an Input Area and then a Report Area

The input area will have one column B&C

The Reporting area will then lookup data as required

How you extract records for the reporting is then up to you, but it will simplify all the calculations doing it this way
PaulFogel123 likes this.
8. ### PaulFogel123Member

Messages:
129
That's basically how I have most of it organized now. If it exists in the data, I'll extract what I need for the tables from that source. It provides a single source of truth, as they say. But not everything is elsewhere to be looked up, such as many derived ratios. Instead of creating those someplace and then extracting them for the table, I create what I need right in the table.
Hui likes this.