SUMPRODUCT Conditional Formatting

[Cstrauss]

Hello all,

Attempting to write a weighted average with multiple conditions. Having trouble with multiple aspects of the formula.

1)Instead of being equal too text (e.g. --(F:F="Apples") I attempted wrote my formula as you would to be NOT equal to the text (e.g --(F:F<>"Oranges")). This is because there are multiple unique variables which exist in my data so it is easier to just exclude "Apples". I could manipulate the data so that these multiple variables all roll up to one unique variable "NOT APPLES", but would prefer to keep the current structure.

2)One Condition I wrote is greater than a date. It is linked to a cell but I attempted to write this as you would for an IF Condition (E:E>=&A1)

This could be a simple quotation mark or other formatting error. Finding information on this specific situation was difficult. Any help is greatly appreciated Actual Formula I used is below:

=SUMPRODUCT(--('Loan Level Data Query'!$B:$B='Lookup Logic'!B1),--('Loan Level Data Query'!$L:$L>='Lookup Logic'!B2),--('Loan Level Data Query'!$E:$E<>"Conventional"),--('Loan Level Data Query'!$J:$J<>"Corespond"),('Loan Level Data Query'!$D:$D,'Loan Level Data Query'!$F:$F))/SUM('Loan Level Data Query'!$D:$D)

[6StringJazzer]

Welcome to the Forum Cstrauss!

I do not see anything logically or syntactically wrong with your formula based just on what you described. Yet it still might not be the right formula, depending on what your data looks like.

I do see one little hiccup. Please check your data to see if column J of sheet Loan Level Data Query has any values that say "Corespond". This is a spelling error and maybe your formula should say "Correspond".

If that is not your problem then it will help to attach your file. The paper clip icon does not work for attachments. To attach a file, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.

[Cstrauss]

File is attached. expected outcome is 697.8

[6StringJazzer]

How did you determine what the answer should be? This is how your formula breaks down.

=SUMPRODUCT(

--('Loan Level Data Query'!$B:$B='Lookup Logic'!B1), OK

--('Loan Level Data Query'!$L:$L>='Lookup Logic'!B2), OK

--('Loan Level Data Query'!$E:$E<>"Conventional"), OK

--('Loan Level Data Query'!$J:$J<>"Correspond"), OK (updated; see note in my next post)

('Loan Level Data Query'!$D:$D,'Loan Level Data Query'!$F:$F) I don't understand what you're trying to do with this last part, this may be causing the error

)

/SUM('Loan Level Data Query'!$D:$D) Assuming you really want to divide by the sum of all loans in the entire list to get a percentage or something.


I just noticed your title--your post doesn't seem to have anything to do with conditional formatting. Please consider revising the title.

[Cstrauss]

Using filters I moved all of the relevant data onto a new tab then did a weighted avg using SUMPRODUCT('Loan Level Data Query'!$D:$D,'Loan Level Data Query'!$F:$F)/SUM('Loan Level Data Query'!$D:$D). If it is that last part which is causing the error what would you suggest? You do bring up a good point, The Denominator should be SUMIFS('Loan Level Data Query'!$D:$D,'Loan Level Data Query'!$E:$E,"<>Conventional",'Loan Level Data Query'!$J:$J,"<>Correspond",'Loan Level Data Query'!$B:$B,'Lookup Logic'!B1,'Loan Level Data Query'!$L:$L,">="&'Lookup Logic'!B2)as I don't want to divide by the sum of the entire population of loans for the weighted average, just the sample with conditions

I apologize for the title, essentially I'm trying to use these conditions and the SUMPRODUCT function to get a weighted average.

[6StringJazzer]

First I made an error in my analysis above. I overlooked that you are looking for <>"Correspond", so it should include all values.

Your last post cleared up a lot of my questions.

Phew! So this formula gives your expected result:

=SUMPRODUCT(--('Loan Level Data Query'!$B:$B='Lookup Logic'!B1),--('Loan Level Data Query'!$L:$L>='Lookup Logic'!B2),--('Loan Level Data Query'!$E:$E<>"Conventional"),--('Loan Level Data Query'!$J:$J<>"Correspond"),'Loan Level Data Query'!$D:$D,'Loan Level Data Query'!$F:$F)/SUMPRODUCT(--('Loan Level Data Query'!$B:$B='Lookup Logic'!B1),--('Loan Level Data Query'!$L:$L>='Lookup Logic'!B2),--('Loan Level Data Query'!$E:$E<>"Conventional"),--('Loan Level Data Query'!$J:$J<>"Correspond"),'Loan Level Data Query'!$D:$D)