Search results

I don't know that this is easily possible. You can list the totals outside the pivot table, using SUMIFS to your original data. The numbers will change with database changes...

A simple one that I like: Applies a lot in the forums!

You are welcome. I hope that I still have the willpower you have to do any hardcore learning after I am retired! I wanna be sitting in my own little beachfront property in Jamaica or Bahamas when I retire.... sipping my rum punch.

The years in Row 4 of Level2 sheet don't match the years in Row 5 of the other sheet....

You're correct, it should have been TRUE... the 1 is used when there are multiple conditions... Also, you should be able to use EVALUATE method for array formulas... This is a google search result...

I think it should also work as: =SUMIF(INDIRECT("'"&$E8&"'!$F$6:$F$20") ,$F8&"*",INDIRECT("'"&$E8&"'!A$6:A$20")) No need for the SUMPRODUCT.

Actually, I am not even sure why you need the SUMPRODUCT... If I understood, I think you only need: =SUMIF(INDIRECT("'"&$E8&"'!$F$6:$F$20"),$F8&"*",INDEX(INDIRECT("'"&$E8&"'!$G$6:$HZ$20"),0,MATCH(H$4,INDIRECT("'"&$E8&"'!$G$5:$HZ$5"),0)))

Are you trying to match H4 to INDIRECT("'"&$E8&"'!$G$5:$HZ$5")? is H4 a date too, and is it an exact match to what is in INDIRECT("'"&$E8&"'!$G$5:$HZ$5")? Maybe try posting a sample workbook if still an issue...

Try: =SUMPRODUCT(SUMIF(INDIRECT("'"&$E8&"'!$F$6:$F$20"),$F8&"*",INDEX(INDIRECT("'"&$E8&"'!$G$6:$HZ$20"),0,MATCH(H$4,INDIRECT("'"&$E8&"'!$G$5:$HZ$5"),0))))

Also Brian, The MATCH() function as you wrote it would be an Array Formula... not a normal formula.... You can try converting this formula to your VBA =MATCH(1,INDEX(A1&B1=(Sheet3!D$1: D$7)&(Sheet3!F$1:F$7),0),0) which is still an array formula, but handled like a normal formula (i.e. no...

If you have 2007 or later, you can also use IFERROR e.g. =IFERROR(VLOOKUP(),"") will return a null if error is encountered. You can also use SUMIF to ignore #N/A error e.g. =SUMIF(A1:A10,"<>#N/A") sums values in A1:A10 ignoring the #N/A error.

The 3 ranges need to be the same size. $F$6:$F$20 is one column wide whereas $G$6:$HZ$20 is 228 columns wide.

Do you mean? =SUMPRODUCT(SUMIF(INDIRECT("'"&$E8&"'!$F$6:$F$20"),LEFT($F8,14),INDIRECT("'"&$E8&"'!A$6:A$20"))) or =SUMPRODUCT(SUMIF(INDIRECT("'"&$E8&"'!$F$6:$F$20"),$F8&"*",INDIRECT("'"&$E8&"'!A$6:A$20")))

Have a look at the method shown here: http://www.xl-central.com/lookup-single-criteria-multiple-sheets.html

Actually, there is a way to use count across multiple sheets without adding multiple countifs together.... =SUMPRODUCT(COUNTIF(INDIRECT("'"&TabNames&"'!F:F"),Sheet8!A4)) where TabNames is a named range listing the sheets to include in the summary. You can replace TabNames with a range...

I am not seeing that issue. Can you illustrate?

How about? =IFERROR((INDEX($B3:$B$25+$C3:$C$25,MATCH($A2,$A3:$A$25,0))-($D2+$E2))*1440,"")

Assuming your data is in columns A:E, then in F2 try: =(LOOKUP(2,1/($A$2:$A$25=A2),$B$2:$B$25+$C$2:$C$25)-INDEX($D$2:$D$25+$E$2:$E$25,MATCH(A2,$A$2:$A$25,0)))*1440 adjust the ranges to cover your whole database (don't use too many more rows than necessary), then copy down.

I am not sure why you have H2 and H3 filled with "Customer2" if Customer2 occupied room from Apr 3rd to 5th? If that is an error and should show "Free", then try this formula in G2: =IFERROR(INDEX($A$2:$A$10,MATCH(1,INDEX(($B$2:$B$10=G$1)*($C$2:$C$10<=$F2)*($D$2:$D$10>=$F1),0),0)),"Free")...

Brian, I think the syntax would be: =data!$A$1 but somehow, I don't think that is what the OP is wanting. Can you please post a sample workbook showing what you need?