count if and subtract

%3CLINGO-SUB%20id%3D%22lingo-sub-3073217%22%20slang%3D%22en-US%22%3Ecount%20if%20and%20subtract%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3073217%22%20slang%3D%22en-US%22%3E%3CP%3EWhat%20is%20the%20formula%20for%20counting%20and%20then%20subtract%20if%20value%20is%20text%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%3A%20I%20want%20to%20count%20if%20the%20name%20is%20column%20G%20is%20%22Francine%22%20and%20subtract%20.5%20if%20the%20word%20in%20comlun%20E%20says%20Agency.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%221322%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22246%22%3EName%20Name%3C%2FTD%3E%3CTD%20width%3D%22204%22%3EJob%201%3C%2FTD%3E%3CTD%20width%3D%22121%22%3ECity%20A%3C%2FTD%3E%3CTD%20width%3D%22179%22%3EManager%201%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22118%22%3EReferral%3C%2FTD%3E%3CTD%20width%3D%22139%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22141%22%3EFrancine%3C%2FTD%3E%3CTD%20width%3D%22174%22%3EJanuary%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EName%20Name%3C%2FTD%3E%3CTD%3EJob%202%3C%2FTD%3E%3CTD%3ECity%20A%3C%2FTD%3E%3CTD%3EManager%201%3C%2FTD%3E%3CTD%3EAgency%3C%2FTD%3E%3CTD%3E2%2F14%2F22%3C%2FTD%3E%3CTD%3EFrancine%3C%2FTD%3E%3CTD%3EJanuary%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EName%20Name%3C%2FTD%3E%3CTD%3EJob%202%3C%2FTD%3E%3CTD%3ECity%20B%3C%2FTD%3E%3CTD%3EManager%202%3C%2FTD%3E%3CTD%3EInMail%3C%2FTD%3E%3CTD%3E1%2F24%2F22%3C%2FTD%3E%3CTD%3EFrancine%26nbsp%3B%3C%2FTD%3E%3CTD%3EJanuary%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3073217%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

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.