Calculating two separate cells properly

Newbienew

Active Member
Joined
Mar 17, 2017
Messages
395
Office Version
  1. 2016
Platform
  1. Windows
=SUM(COUNTIFS($B$3:$B$53,E57&"*",$U$3:$U$53,{"AFF","ENL"}&"*"))

So first let me say thank you to whom ever is willing to help out it is greatly appreciated. Above is my formula for counting one cell if another cell meets the either requirement. The problem that I am having is, is that, the formula works but if cell E57 is blank, the cell the formula is in still gives me a total number instead of just being 0.

If E57 is blank I would like for the cell the formula is in to be 0.

The only way that is works is if I type in place of E57, the actual words or name I want. "Jason" Issue here is I am trying to make up for typos with a space after the name. So both still are counted (Jason) and (Jason )

Problem with this is this formula is in 6 cells in the column. So that means every time someone new needs to be added, I or someone else has to unlock the spreadsheet and manually change the formula instead of just using the unlocked cell that is permitted. Is there any thing i can do so it doesn't still calculate all the AFF and ENL even if the entry cell is blank.
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Hi,

Just add an IF test:

Code:
=IF(E57="",0,SUM(COUNTIFS($B$3:$B$53,E57&"*",$U$3:$U$53,{"AFF","ENL"}&"*")))
 
Upvote 0
WOW, Thank you so much, that has got to be the fastest response I think i have ever got so far. Thank you so much.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top