# INDIRECT Function with multiple sheets - SUMIF, INDIRECT & MATCH

1. ## 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)))

2. ## Re: INDIRECT Function with multiple sheets - SUMIF, INDIRECT & MATCH

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.

3. ## Re: INDIRECT Function with multiple sheets - SUMIF, INDIRECT & MATCH

Formula:
`Please Login or Register  to view this content.`

Check the attached file...

4. ## Re: INDIRECT Function with multiple sheets - SUMIF, INDIRECT & MATCH

Originally Posted by Vikas_Gautam
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.

5. ## Re: INDIRECT Function with multiple sheets - SUMIF, INDIRECT & MATCH

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.

6. ## Re: INDIRECT Function with multiple sheets - SUMIF, INDIRECT & MATCH

Thanks for the response DJ..

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1