IF Function Combined with Median date range check across multiple sheets

[storm170]

Hello all,

I apologize but I cannot download the html creator while at work due to IT locking that function down so hopefully I can describe my question in a way that makes sense.

I am trying to create a formula that checks if a single cell date falls between a beginning and end date using the median function, and if so returns a value. I have multiple sheets I would like the check to do, or alternatively an array of cells for both the date checked and the value returned.
This is the formula I have created so far for an individual check, where 01 XXX is a sheet name, G6 is the cell with the date being checked, C26 is a beginning date and E26 is an ending date, and G40 is the value being returned if this check is true.

=IF(MEDIAN('01 XXX'!G6,$C$26,$E$26),'01 XXX'!G40,)

The formula I have been trying to get working for checking each sheet is below, with all the information the same except A28:A29 is a range of the sheet names I would be trying to pull data from.

=SUMPRODUCT(SUMIF(INDIRECT("'"&A28:A29&"'!G6"),MEDIAN((INDIRECT("'"&A28:A29&"'!G6"),$C$26,$E$26,INDIRECT("'"&A28:A29&"'!G40")))))

I have not started work on a formula that would check an individual sheet but would check an array of cells, ie G6:AD6 and return respective values, G40:AD40.

Please let me know if I need to supply additional information.

 

[bbott]

Welcome to the board.

Your IF formula will always evaluate to TRUE since the median of 3 positive numbers will never be 0. How about something like:

=IF(AND(G6>=C26,G6<=E26),G40,"")

 

[storm170]

Thank you for your reply, I apologize for the error above. I have adjusted the formula so many times that I believe I copied an older version I was still tinkering with. The formula below is similar to yours but still uses the MEDIAN function, which I am trying to adapt to check multiple sheets or an array of cells within a single sheet. Any advice would be appreciated!

=IF('01 XXX'!G6=MEDIAN('01 XXX'!G6,$C$26,$E$26),'01 XXX'!G40,0)

 

[storm170]

Any ideas on this matter would be greatly appreciated.

 

[Domenic]

If you only have two (or relatively few) worksheets, I'd stick with your original formula (your first IF function + your second IF function, and so on). Otherwise, you can adopt the following formula...

=SUMPRODUCT(SUMIFS(INDIRECT("'"&A28:A29&"'!G40"),INDIRECT("'"&A28:A29&"'!G6"),">="&$C$26,INDIRECT("'"&A28:A29&"'!G6"),"<="&$E$26))

Hope this helps!

 

[storm170]

Thank you so much, Domenic! That works amazingly well; I should have taken prior suggestions about replacing my MEDIAN formula with the >= <= version. Marking this as solved.

 

[Domenic]

You're very welcome! Glad I could help!