COUNTIF Quandary

[farabi]

Hi All,


I am putting together a report that can be automatically repeated on a monthly basis from budget reports that I receive at the start of each month (which I'll call 'Budget Report').


I have managed to calculate the first value I need: the number of placements I have in a particular region at the start of the month. The formula is:


=SUM(COUNTIFS('[Report - November2016.XLSB]Main'!$A:$A,"South East",''[Report - November2016.XLSB]Main'!$I:$I,"Urgent",'[Report - November2016.XLSB]Main'!$M:$M,{">=$B7",""}))


This returns a count of placements in:
a) the South East,
b) in the 'Urgent' category, which
c) have not terminated until at least the Start of this month (that is, a count of cells greater than or equal to the first day of the present month, or blank, in a column of dates).


The next entry I need to prepare is along similar lines, but I cannot figure out how to do it.


In Budget Report I have a column, P, of provider names.


In another spreadsheet I'll call 'Contracts Reference', I have all of these providers in column A, with another column, H, detailing 'contract status' for each of these providers.


In this entry I am making I need to count the same as in my first entry (i.e. South East, Urgent, current at start of month) from Budget Report, but only those which ALSO have the contract status value 'Restricted' in Contracts Reference.


How do I do this?


Thanks.

 

[barry houdini]

Re: COUNTIF Quandry

Try like this:

=SUM(COUNTIFS('[Report - November2016.XLSB]Main'!$A:$A,"South East",''[Report - November2016.XLSB]Main'!$I:$I,"Urgent",'[Report - November2016.XLSB]Main'!$M:$M,{">=$B7",""}),'[Report - November2016.XLSB]Main'!$P:$P,IF('[Contracts Reference.xlsx]Sheet1'!$H$2:$H$100="Restricted",'[Contracts Reference.xlsx]Sheet1'!$A$2:$A$100))

Needs to be confirmed with CTRL+SHIFT+ENTER

change references as required

 

[farabi]

Re: COUNTIF Quandry

Thank you very much for your help, but it seems to be returning an identical figure to the first formula rather than a much, much smaller one.

 

[barry houdini]

Did you confirm with CTL+SHIFT+ENTER?

You need to select the cell with the formula, press F2 key to select formula then hold down CTRl and SHIFT keys while pressing ENTER. If done correctly you will see curly braces like { and } around the formula in the formula bar