CODEWITHSUNDEEP
excelexcel-formula
Insider
Insider

Reputation: 105

How do I return a blank if there is no data on a separate sheet?

I want data in F16 sheet STATISTICS to only be present when there is a W in column P on sheet TRADE LOG between the dates 01 Feb 2021 and 28 Feb 2021.

If there is no WI want F16 to return a blank.

This is the formula I am currently using:

=IF(ISBLANK(INDIRECT(CONCATENATE("E",ROW()))), "", COUNTIFS('TRADE LOG'!P:P,"W",'TRADE LOG'!B:B,">="&DATE(2021,2,1),'TRADE LOG'!B:B,"<="&DATE(2021,2,28)))

I anyone could suggest any tweaks that would allow me to do this I would be grateful.

Upvotes: 0

Views: 58

Answers (1)

Forward Ed
Forward Ed

Reputation: 9894

place the following formula in F16 and replace "Data" with the formula or data you want displayed when there is a "W" in your hard coded date range. Do note that the "W" in the P column is not case sensitive.

=IF(COUNTIFS('Trade Log'!P:P,"W",'Trade Log'!B:B,">="&DATE(2021,2,1),'Trade Log'!B:B,"<="&DATE(2021,2,28)),"DATA","")

You may also want to look at placing the month you want to check or date range as a cell reference instead of a hard coded date, but you will need to make that decision based on your needs.

POC

In order to display the "W"s within date criteria, replace "Data" with the part of the formula you used to make the count in the first place.

COUNTIFS('Trade Log'!P:P,"W",'Trade Log'!B:B,">="&DATE(2021,2,1),'Trade Log'!B:B,"<="&DATE(2021,2,28))

When substituted into the original formula it will look like the following:

=IF(COUNTIFS('Trade Log'!P:P,"W",'Trade Log'!B:B,">="&DATE(2021,2,1),'Trade Log'!B:B,"<="&DATE(2021,2,28)), COUNTIFS('Trade Log'!P:P,"W",'Trade Log'!B:B,">="&DATE(2021,2,1),'Trade Log'!B:B,"<="&DATE(2021,2,28)),"")

Alternate Approach

You could also just use the countifs formula and use custom formatting for the cell so that a zero value is displayed as nothing or space.

Upvotes: 1

Related Questions