count if and subtract

Copper Contributor

What is the formula for counting and then subtract if value is text? 

 

Example: I want to count if the name is column G is "Francine" and subtract .5 if the word in comlun E says Agency.

 

Name NameJob 1City AManager 1 Referral FrancineJanuary
Name NameJob 2City AManager 1Agency2/14/22FrancineJanuary
Name NameJob 2City BManager 2InMail1/24/22Francine January
1 Reply

@Francineporter 

For example:

=SUMPRODUCT((G1:G25="Francine")/(1+(E1:E25="Agency")))

Adjust the ranges as needed.

If you don't have Microsoft 365 or Office 2021, confirm the formula with Ctrl+Shift+Enter.