Feb 16 2022 10:20 AM
Hi Team ,
I need one help with one formula . Below is the screen shot with the scenario .
Here is the case :
Scenario 1
I want a new column named Weightage , If the column name is Attended then it should be multiplied by 1.5 and so on with the other values , even if the column order changes I mean if today column A is Attended then we multiplied with 1.5 , but tomorrow if Column B is Attended then automatically the formula should multiply column B with 1.5 .
Attended | 1.5 |
Content Syndication | 0.5 |
Other | 0.5 |
Outbound Telemarketing Qualified | 0.5 |
Request Rep Email | 1.5 |
Scheduled Consultation Occurred | 1.5 |
Web Response | 1 |
Scenario 2 :
I need help in creating formula which calculates the count of all "True" Values for a given range .
Quick response is deeply appreciated !!
Thanks in Advance
Shiv
Feb 16 2022 10:32 AM
Hi Team , here is the updated full view of the excel
Feb 16 2022 10:52 AM
Feb 16 2022 11:00 AM
=SUMPRODUCT(($A$1:$A$7=$A$9:$G$9)*($B$1:$B$7*A10:G10))
Maybe with this formula as shown in the attached file.
=COUNTIF(I10:I17,"WAHR")
This formula is in cell I20 to count the number of TRUE (WAHR in german).
Feb 16 2022 11:15 AM
Feb 16 2022 11:27 AM
Can you check if "TRUE " with a space at the end is entered in range O42:O107 instead of "TRUE" without a space.
Feb 16 2022 11:39 AM
SolutionIf true/false are texts, not Boolean, when
=SUMPRODUCT(--( $E$3:$E$13="TRUE") )
COUNTIF() in background transform texts which represent numbers and Booleans accordingly.
Feb 16 2022 11:39 AM
Thanks for your reply
Column O is a having a formula.
For Scenario 1 ,
=SUMPRODUCT(($A$1:$A$7=$A$9:$G$9)*($B$1:$B$7*A10:G10))
This works when the columns are 7 as soon as I have only 2 columns then it starts giving wrong results , The issue is in week 1 week have data for all 7 columns but in week 2 we have only for 3 , Hence as soon as I change to week 2 it gives me wrong results .
Here is the formula what you have given : =SUMPRODUCT(($X$3:$X$9=$B$4:$G$4)*($Y$3:$Y$9*B6:G6))
Here is the updated one : =SUMPRODUCT(($X$3:$X$9=$B$4:$D$4)*($Y$3:$Y$9*B5:D5))
Feb 16 2022 11:43 AM
Feb 16 2022 11:39 AM
SolutionIf true/false are texts, not Boolean, when
=SUMPRODUCT(--( $E$3:$E$13="TRUE") )
COUNTIF() in background transform texts which represent numbers and Booleans accordingly.