Kelli has attended:
Word Intermediate course
Excel Formulas
Hello,
Some of my formulas on my workbook are not calculating. There is no error in the formula (that I can see) and I have checked that there are no spaces, it is on automatic calculation.
The formula is below:
=SUMPRODUCT(IF(C1:C5000="AV",1,0),IF(E1:E5000="Enquiry",1,0))
Can anyone help me?
Thanks,
Kelli
RE: Excel Formulas
Hi Kelli,
Thank you for the forum.
There is nothing wrong with the formula. I assume it returns a zero.
See my Countifs below. Isn't what you try to do???
=countifs(C1:C5000,"AV",E1:E5000,"Enquiry")
Test what is wrong:
Type "AV" in a blank cell (Lets imaging you do it in cell A1) . In another blank cell type =Exact(A1,C1) (lets imaging you have AV in C1).
If the Exact function return True it is the exact same content in the two cells. If it returns False you have something hidden in the cells in column C.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Excel Formulas
Hi Kelli,
If what you want is to count have many rows you, AV and Enquiry in the same row, the Sumproduct should look like this:
=SUMPRODUCT((C1:C5005="AV") * (E1:E5005="Enquiry"))
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
