Formulas over several sheets and cells

[Ziro54]

Hi,

I am not sure if there is a solution to this. I have a Workbook where I capture information. I have added some formulas ease the process. I have a Dashboard Worksheet that summarize some data. The formula is [COUNTIFS('Incident Register'!$B$5:$B$2001,'Data Sheet'!A4,'Incident Register'!$M$5:$M$2001,"Yes")]. I want [IF(B4="","",) in front. I get a [#value] error on my Dashboard. The formula on my DashBoard is [IF(N6<>"",(SUM(('Data Sheet'!$A$4:$R$1000>=$E$9)*('Data Sheet'!$A$4:$A$1000<=$N$6)*('Data Sheet'!$R$4:$R$1000))),"")]. N6 you enter today's date manually.

Is there something else I can use? Or a way to fix this?

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also what cell is the countifs formula in?

 

[Ziro54]

Office 365.

 

[Ziro54]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also what cell is the countifs formula in?

Countif is in datasheet!R4

 

[Fluff]

Thanks for that & for updating your profile.
How about

=IF(N6="","",SUM(FILTER('Data Sheet'!$R$4:$R$1000,('Data Sheet'!$A$4:$R$1000>=$E$9)*('Data Sheet'!$A$4:$A$1000<=$N$6)*('Data Sheet'!$R$4:$R$1000<>""),0)))

 

[Ziro54]

=IF(N6="","",SUM(FILTER('Data Sheet'!$R$4:$R$1000,('Data Sheet'!$A$4:$R$1000>=$E$9)*('Data Sheet'!$A$4:$A$1000<=$N$6)*('Data Sheet'!$R$4:$R$1000<>""),0)))

Same error, Added two photos, not sure if that will help?

 

[Fluff]

Just spotted the typo in your original formula. The R should be an A

'Data Sheet'!$A$4:$R$1000>=$E$9)

 

[Ziro54]

Same error, Added two photos, not sure if that will help?

Still getting [#value] error, with the correction.

 

[Fluff]

In that case check that you don't have any cells on the Data Sheet with a #Value error

 

[Ziro54]

In that case check that you don't have any cells on the Data Sheet with a #Value error

I Have managed to fix it changing the formula to the following:

[=IF(B5="","",SUM(COUNTIFS('Data Log'!$C$2:$C$2001,{"Ablution Facilities","Canteen","Locker Room","Office","Shower Room","Yard"},'Data Log'!$A$2:$A$2001,'Data Sheet'!$A5,'Data Log'!$G$2:$G$2001,"SC")))]