How Can I Speed Up Windows?: Fast Solution to Problem: countif for in live document

Friday, September 23, 2016

Fast Solution to Problem: countif for in live document

countif for in live document

Hi All,

I am trying to simplify a worksheet i am using and i would like to automate a formula, The issue is that i cant use pivot tables because this is a shared workbook and the item i would like to count is part of a dashboard that has already been created.

So to the problem...

There are 2 worksheets utalised by 2 employees assigned to an individual work sheet.
the worksheet is a live record of jobs going through a process and there are 2 variables that are important.

Variable 1 is an input date in column A, this is manual entry and remains in the same cell in the same row in perpetuity.
now jobs are added over time below this.

Variable 2 is a drop down selection in column B, this is either "Business" or "Consumer".

I would like to count how many Business and Consumer jobs there are in each month as they are inputted into the spreadsheet.

The formula i have at the moment to count total jobs is below, but i cant seem to solve for the Business or Consumer portion:

=SUMPRODUCT(('Sheet 1'!$E$3:$E$799<>"")*(MONTH('Sheet 1'!$E$3:$E$799)=1))

Anwsers to the Problem countif for in live document

Download Error Fixer for Free Now

Your formula seems a bit redundant as it checks both for the month in column E and then that the month in column E is numerically 1.
If the month is equal to 1, then there will be a value in column E.
Perhaps teh month was supposed to be column A.
Either
replace the first condition with a comparison to either Business or Consumer in column B or change the month criteria to column A and add a condition for either Business or Consumer.

=SUMPRODUCT(('Sheet 1'!$B$3:$B$799="Business")*(MONTH('Sheet 1'!$E$3:$E$799)=1))

...
or,

=SUMPRODUCT(('Sheet 1'!$B$3:$B$799="Business")*(MONTH('Sheet 1'!$A$3:$A$799)=1)*SIGN(LEN('Sheet 1'!$E$3:$E$799)))

FYI, SIGN(LEN('Sheet 1'!$E$3:$E$799)) is the same as 'Sheet 1'!$E$3:$E$799<>"")

Use the System File Checker tool to repair corrupted or missing system files:

Click Start.
Type cmd, and then press CTRL+SHIFT+ENTER to open the elevated command prompt (I.e. Command prompt with administrative rights).
The User Account Control window should appear, be sure that the action it exhibits is correct, and then press the Continue button.
Type or copy paste the below command line into it without quotes:
"Sfc /scannow"

Another Safe way to Repair the Problem: countif for in live document:

How to Fix countif for in live document with SmartPCFixer?

1. Click the button to download SmartPCFixer . Install it on your computer. Open it, and it will perform a scan for your system. The errors will be shown in the list.