Remember Me?

#1
July 15th 19, 05:15 AM posted to microsoft.public.excel.programming
 external usenet poster First recorded activity by ExcelBanter: Sep 2012 Posts: 44
Sum product formula with conditions

Hi all
Good day to everyone

I have a excel sheet
Column 1 is Department
Column 2 is weightage
Column 3 is rating of project
Column 4 is filled with either 65% or 35%

I want to sum product of column 2 and 3 based on conditions of column 1 and 2

Can someone share the logic how I can derive this

#2
July 16th 19, 12:52 AM posted to microsoft.public.excel.programming
 external usenet poster First recorded activity by ExcelBanter: Jul 2019 Posts: 1
Sum product formula with conditions

Simply use sumif, and / or
#3
July 16th 19, 04:01 AM posted to microsoft.public.excel.programming
 external usenet poster First recorded activity by ExcelBanter: Sep 2012 Posts: 44
Sum product formula with conditions

Sumif is sum of selected values based on condition

Here I want to multiply column 2 respective weightage and column 3 respective ratings based on department. Some department 65% payment & remaining department 35%
#4
July 16th 19, 04:40 PM posted to microsoft.public.excel.programming
 external usenet poster First recorded activity by ExcelBanter: Oct 2016 Posts: 18
Sum product formula with conditions

On Tuesday, 16 July 2019 04:01:30 UTC+1, TIMOTHY wrote:
Sumif is sum of selected values based on condition

Here I want to multiply column 2 respective weightage and column 3 respective ratings based on department. Some department 65% payment & remaining department 35%

Hi
Isn't it just
=B2*C2*D2
since column D contains the Percentage relevant to the Department.
Column D could be a Lookup to a table of Departments and Percentages.
#5
July 17th 19, 03:46 PM posted to microsoft.public.excel.programming
 external usenet poster First recorded activity by ExcelBanter: Sep 2012 Posts: 44
Sum product formula with conditions

Thank you Alan & Rover

I have searched on google and found out the formula

=sumproduct(--(Range1,criteria1),--(Range2,criteria2),Range3,Range4)

In this case Range3 & 4 are column 2&3

#6
July 17th 19, 05:34 PM posted to microsoft.public.excel.programming
 external usenet poster First recorded activity by ExcelBanter: Jan 2018 Posts: 85
Sum product formula with conditions

On 7/17/2019 9:46 AM, TIMOTHY wrote:
Thank you Alan & Rover

I have searched on google and found out the formula

=sumproduct(--(Range1,criteria1),--(Range2,criteria2),Range3,Range4)

=sumproduct(num(Range1,criteria1),num(Range2,crite ria2),Range3,Range4)

I don't know who started this idiom of a double-negation operator
instead...but it's a least confusing to read if nothing else and seems
less efficient besides.

--

#7
July 17th 19, 09:20 PM posted to microsoft.public.excel.programming
 external usenet poster First recorded activity by ExcelBanter: Apr 2015 Posts: 1,184
Sum product formula with conditions

On 7/17/2019 9:46 AM, TIMOTHY wrote:
Thank you Alan & Rover

I have searched on google and found out the formula

=sumproduct(--(Range1,criteria1),--(Range2,criteria2),Range3,Range4)

=sumproduct(num(Range1,criteria1),num(Range2,crite ria2),Range3,Range4)

I don't know who started this idiom of a double-negation operator
instead...but it's a least confusing to read if nothing else and seems less
efficient besides.

The double negative refs only negative values in the specified ranges. In other
uses it forces a negative value.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
#8
July 18th 19, 12:58 AM posted to microsoft.public.excel.programming
 external usenet poster First recorded activity by ExcelBanter: Jan 2018 Posts: 85
Sum product formula with conditions

On 7/17/2019 3:20 PM, GS wrote:
On 7/17/2019 9:46 AM, TIMOTHY wrote:
Thank you Alan & Rover

I have searched on google and found out the formula

=sumproduct(--(Range1,criteria1),--(Range2,criteria2),Range3,Range4)

=sumproduct(num(Range1,criteria1),num(Range2,crite ria2),Range3,Range4)

I don't know who started this idiom of a double-negation operator
instead...but it's a least confusing to read if nothing else and seems
less efficient besides.

The double negative refs only negative values in the specified ranges.
In other uses it forces a negative value.

Not what I observed in the earlier cases wherein I was looking at how to
do some fairly complicated similar things...extensive testing appeared
to show it is simply what it is--a double negation that leaves the
result unchanged.

It was used by Klaus in his answers apparently to force a reference that
without Excel did not return values. NUM() is much cleaner for the purpose.

I'd surely be interested in seeing any reference that explains any other
interpretation for the syntax--I was unable to find it anywhere in the
documentation.

--

#9
July 18th 19, 04:53 PM posted to microsoft.public.excel.programming
 external usenet poster First recorded activity by ExcelBanter: Apr 2015 Posts: 1,184
Sum product formula with conditions

I'd surely be interested in seeing any reference that explains any other
interpretation for the syntax--I was unable to find it anywhere in the
documentation.

Yeah, you'll do better googling it. Try...

using double negative in Excel formula

I could be wrong about its usage in Excel just off using so many different

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
#10
July 18th 19, 05:29 PM posted to microsoft.public.excel.programming
 external usenet poster First recorded activity by ExcelBanter: Jan 2018 Posts: 85
Sum product formula with conditions

On 7/18/2019 10:53 AM, GS wrote:
*using double negative in Excel formula

"This method is mostly used in SUMPRODUCT formulas to convert the
conditional arrays that evaluates to TRUE/FALSE into 1/0."

Yes, that's exactly what N() is for...instead of the double-negative. I
part.

--

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Nastyashman Excel Worksheet Functions 4 July 6th 09 05:48 PM sltchsyi Setting up and Configuration of Excel 0 May 4th 09 01:32 AM hupjack Excel Programming 0 April 9th 09 11:57 PM chelledc Excel Worksheet Functions 5 July 31st 08 02:32 PM [email protected] Excel Programming 0 October 2nd 06 03:54 AM

All times are GMT +1. The time now is 09:08 AM.