cant figure out how to return correct number

[rlleblan1]

I want to create a function that will combine the functions below and return the number of cells that meet the first formula and also the adjacent cell meets the second formula.

1. =COUNTIFS(Sheet1!A1:ZZ494,">="&B4,Sheet1!A1:ZZ494,"<="&D4)+COUNTIFS(Sheet2!A1:ZZ494,">="&B4,Sheet2!A1:ZZ494,"<="&D4)+COUNTIFS(Sheet3!A1:ZZ494,">="&B4,Sheet3!A1:ZZ494,"<="&D4)+COUNTIFS(Sheet4!A1:ZZ494,">="&B4,Sheet4!A1:ZZ494,"<="&D4)+COUNTIFS('Target account related contact '!A1:ZZ494,">="&B4,'Target account related contact '!A1:ZZ494,"<="&D4)


***** B4 and D4 are dates user can change

2. =COUNTIF(Sheet1!AI:AAB,A9)+COUNTIF(Sheet2!AI:AAD,A9)+COUNTIF(Sheet3!AI:AAA,A9)+COUNTIF(Sheet4!AI:ZZ,A9) +COUNTIF('Target account related contact '!AI:ZZ,A9)

****** A9 refers to a cell with specific word

If cell in workbook matches value between B4 and B5, dates inputted by user, look at adjacent cell and if it matches value in A9 count.

Thanks in advance for assistance.

 

[rlleblan1]

Could really use help with this