COUNTIFS

Resolved · Medium Priority · Version 2010

Wed 26 Jul 2017, 15:29 Delegate Hannah said...

Hannah has attended:
Excel Dashboards for Business Intelligence course

COUNTIFS

Hi,

I'm trying to construct a COUNTIFS formula but am struggling with the last element. I would like it to count records that have:

- 'Achieved' in column B
- '2014/15' in column W
- a date in column W that is before or equal to the date in column U + 90 days.

Many thanks,

Hannah

Fri 28 Jul 2017, 13:55 Trainer Sarah said...

RE: COUNTIFS

Hi Hannah

Thanks for your question.

The answer will depend on the way you've set your spreadsheet up.

Can I ask what format the data in column W is in? Is it text eg '2014/15' or date eg '01/01/15'?

Regards

Sarah
Excel Trainer

Mon 31 Jul 2017, 15:14 Delegate Hannah said...

RE: COUNTIFS

Hi,

I made an error with the question I asked, it should have read:

I'm trying to construct a COUNTIFS formula but am struggling with the last element. I would like it to count records that have:

- 'Achieved' in column B (plain text)
- '2014/15' in column W (plain text)
- a date in column X that is before or equal to the date in column V + 90 days. (date format)

Many thanks,

Hannah

Mon 31 Jul 2017, 16:53 Trainer Sarah said...

RE: COUNTIFS

Hi Hannah

I understand why you're having trouble with this, it's a bit fiddly!

In your last criteria you want to test each row individually to see if the date in each cell in column X<= the date in each corresponding cell in column V + 90, so you need to take one more step before using your COUNTIFS.

Try inserting a new column (let's say Y) and use an IF formula to test:

=IF(X1<=V1+90,TRUE,FALSE)

You can then use this column Y in your COUNTIFS:

=COUNTIFS(B:B,"Achieved",W:W,"2014/15",Y:Y,"TRUE")

Let me know if this works and if you have any further questions.

Regards,

Sarah

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Move to edge of data block

When data is held in a block, however large, use the Ctrl key with your cursors to move quickly to the far edge of the block. Works with all 4 direction keys.

View all Excel hints and tips