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

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
 

Attachments

  • Sample.xlsb
    544.7 KB · Views: 5
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
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.
 
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
 
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?
 
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?
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.
 
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
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
 
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
 
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
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.
 
  • Like
Reactions: Hui
Back
Top