INDIRECT Function with multiple sheets - SUMIF, INDIRECT & MATCH

Hello All,

I received a lot of help yesterday on the forum but I have hit yet another snag. The first formula below which is in cell N10 is returning a 0 and I'm not sure why but it should be a 1. I am trying to reference cells C:Q ("C"&MATCH(E10,RL!A:A,0)&":Q"&MATCH(E10,RL!A:A,0)) on sheet RL in the indirect formula but I don't know what I am missing. Any help is appreciated! Thank you.

=SUMIF(RL!C$28:Q$28,">="&M10,INDIRECT("C"&MATCH(E10,RL!A:A,0)&":Q"&MATCH(E10,RL!A:A,0)))

This formula is in cell N11 which works if the information is all on the same sheet

=SUMIF(S$9:AG$9,">="&M11,INDIRECT("S"&MATCH(E11,Q:Q,0)&":AG"&MATCH(E11,Q:Q,0)))

Originally Posted by Vikas_Gautam

I have made a small adjustment in your formula...

Formula:

Please Login or Register  to view this content.

Check the attached file...

Awesome! It works just as I wanted it to. I was so close! Thank you very much for your help.

Originally Posted by Richard Buttrey

Hi,

Why do you believe that Item Ref 444 on 9/25/14 should return a total of 1?
Cell H29 (which is the intersection of 9/25/14 and 444) is zero.

One other Q. In your real world production workbook are there many rows for the same Item Ref? If not you don't need SUMIF.

The item number would typically only show once on MD! but depending on the event column it may show multiple times. I thought the SUMIF function was referring to comparing the date on MD! column M to the date on the corresponding row of the item ref, MD! column E, and then to SUMIF values greater or equal to the date MD! Column M. Is there a more efficient way? I want to make this as light as possible.