Help with Sumif and INDIRECT

[JHL]

Hello,
I’m using the formula below to sum multiple sheets that has a code in cell
D1. I would like to modify if possible to accommodate one more criteria in
cell F1 of the summary sheet. The references for E:E and B:B should remain
the same.

I obtained this formula awhile back using this forum.

Thank you.

=SUMIF(INDIRECT("'"&A2&"'!E:E"),summary!$D$1,INDIRECT("'"&A2&"'!B:B"))

 

[Pete_UK]

You can only use SUMIF for a single criteria. If your criteria is that
D1 AND F1 have to be met then you will need to use SUMPRODUCT.

Depending on what your criteria for F1 is, though, an alternative
might be that you can concatenate two columns into a helper column and
then you could still use SUMIF on this helper column - faster than SP.

Give us a few more details, and I'll be able to advise further.

Hope this helps.

Pete

 

[JHL]

Pete thanks,
I'm not sure what you mean by helper column. Currently "D" in the multiple
sheets has words like 'pending', 'released', 'deleted'. My additional column
would be a date field. mm/dd/yy.

I guess can use SumProd, we can't get a helper, but I like the speed of what
I currently have.

 

[Pete_UK]

So presumably you enter a word like "pending" in D1 on the summary
sheet, and you now want to enter a date in F1, and you want the sum of
column B (from the sheet stored in A2) where column E = summary!D1 and
column ? = summary!F1 ?

You can do this with SP, but you need to realise that you can't have
full-column references with SP (unless you are using XL 2007). This is
what it would look like:

=SUMPRODUCT((INDIRECT("'"&A2&"'!E1:E1000")=$D$1)*(INDIRECT("'"&A2&"'!
G1:G1000")=$F$1)*(INDIRECT("'"&A2&"'!B1:B1000"))

where I have assumed that your dates are in column G on those other
sheets, and that you may have up to 1000 rows in your data sheets.
I've also assumed that the formula is in the summary sheet, so you
don't need to specify the sheet name in front of D1, F1.

What I meant by using a helper column is that you could have a formula
like:

=E1&G1

in (say) Z1 of your data sheets, copied down as far as you need.

Then you could have:

=SUMIF(INDIRECT("'"&A2&"'!Z:Z"),$D$1&$F$1,INDIRECT("'"&A2&"'!B:B"))

which will operate as quickly as your current formula. The SP version
may be significantly slower, depending on how many rows you operate it
over. Both should give the same results, however.

Hope this helps.

Pete

 

[Bob Phillips]

A helper cell would be where you have an AND test for the two criteria in
the INDIRECTed sheet, and you test for that column being TRUE instead of
directly for the values.

 

[Pete_UK]

I've just spotted that I missed a bracket from the end of the SP
formula - should be this:

=SUMPRODUCT((INDIRECT("'"&A2&"'!E1:E1000")=$D$1)*(INDIRECT("'"&A2&"'!
G1:G1000")=$F$1)*(INDIRECT("'"&A2&"'!B1:B1000")))

Hope this helps.

Pete

 

[JHL]

I pasted your formula, but I'm getting an #Value! error. I did change the
cell range to 2000 rows where you indicated. but it's not summing. Would
the format of the date cell maybe cause this? Where I have the dates, both
are formatted like
nn-Mon-yy

copied formula:
=SUMPRODUCT((INDIRECT("'"&A2&"'!E1:E2000")=$D$1)*(INDIRECT("'"&A2&"'!
G1:G2000")=$F$1)*(INDIRECT("'"&A2&"'!B1:B2000")))


I also tried the $D$1&$F$1, and it yielded $0.00. no error, but no
calculation. I verified there should be a total.

 

[JHL]

Bob, thanks, if the date field would not be consecutive, would that work?
and would I have to enter pending, and various dates until the formula worked?

 

[Pete_UK]

So I was right in supposing that the dates were in column G, or did
you forget to change that to your own column?

I assumed that you were working with proper date values, and so the
formatting does not affect things. However, if one (eg F1) is a text
value and the other is a true date, then these will not match. One way
of testing is to format the cell(s) as General, and if they are true
dates then you should see the cell(s) display a number slightly less
than 40,000. If the display does not change, however, then it is a
text value.

Hope this helps.

Pete

 

[JHL]

Pete, the dates in the individual spreadsheets are in column G
The date fields are formatted correctly.

Still have #Value! error