Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old July 15th 19, 05:15 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2012
Posts: 44
Default 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   Report Post  
Old July 16th 19, 12:52 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2019
Posts: 1
Default Sum product formula with conditions

Simply use sumif, and / or
  #3   Report Post  
Old July 16th 19, 04:01 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2012
Posts: 44
Default 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   Report Post  
Old July 16th 19, 04:40 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2016
Posts: 17
Default 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   Report Post  
Old July 17th 19, 03:46 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2012
Posts: 44
Default 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   Report Post  
Old July 17th 19, 05:34 PM posted to microsoft.public.excel.programming
dpb dpb is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2018
Posts: 80
Default 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   Report Post  
Old July 17th 19, 09:20 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,072
Default 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   Report Post  
Old July 18th 19, 12:58 AM posted to microsoft.public.excel.programming
dpb dpb is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2018
Posts: 80
Default 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   Report Post  
Old July 18th 19, 04:53 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,072
Default 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
spreadsheet apps/controls!<g

--
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   Report Post  
Old July 18th 19, 05:29 PM posted to microsoft.public.excel.programming
dpb dpb is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2018
Posts: 80
Default 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
had forgotten the function name is just N instead of NUM, my bad on that
part.

--



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Product Code and Product Description setup Nastyashman Excel Worksheet Functions 4 July 6th 09 05:48 PM
I need a product key for my Trail product, 2007 Microsoft Office s sltchsyi Setting up and Configuration of Excel 0 May 4th 09 01:32 AM
Chose a product and update related product variables hupjack Excel Programming 0 April 9th 09 11:57 PM
Product - Conditions - Edited Results chelledc Excel Worksheet Functions 5 July 31st 08 02:32 PM
Vlookup code product and to copy commentary with photo of the product in vba [email protected] Excel Programming 0 October 2nd 06 03:54 AM


All times are GMT +1. The time now is 01:23 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017