SOLVED

Help required in a formula

Occasional Contributor

Hi Team ,

I need one help with one formula . Below is the screen shot with the scenario .Screenshot 2022-02-16 at 11.38.35 PM.png

 

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 .

Attended1.5
Content Syndication0.5
Other0.5
Outbound Telemarketing Qualified0.5
Request Rep Email1.5
Scheduled Consultation Occurred1.5
Web Response1

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

8 Replies

Hi Team , here is the updated full view of the excel

Hi Team , For 2nd Scenario , I am using this formula but it is not working showing 0 : =COUNTIF(E1:E66,"True")

@vshivku1 

=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).

Thank you so much for your response !!
Count is still not working its giving me 0
=COUNTIF(O42:O107,"True")

@vshivku1 

Can you check if "TRUE " with a space at the end is entered in range O42:O107 instead of "TRUE" without a space.

 

best response confirmed by vshivku1 (Occasional Contributor)
Solution

@vshivku1 

If true/false are texts, not Boolean, when

=SUMPRODUCT(--( $E$3:$E$13="TRUE") )

 COUNTIF() in background transform texts which represent numbers and Booleans accordingly.

@Quadruple_Pawn 

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))

Thank you so much Sergei , It works as expected