sum or indirect...formula problem

[bluepenink]

Hi all,

Having some trouble with my formula. Im trying to use indirect to go through the worksheets and give me a total count of invoices and $ amount.

=SUMIF(INDIRECT("'"&A7&"'!'Invoice Count (Combined)'!$A$2:$A$15000"),Sheet5!$B$6,INDEX(INDIRECT("'"&Sheet5!A7&"'!'Invoice Count (Combined)'!$AW$2:$AW$15000"),0,MATCH($C$5,INDIRECT("'"&A7&"'!'Invoice Count (Combined)'!$D$2:$D$15000"),0)))

getting a REF error.

A7 = Invoice Count (Combined) ....name of worksheet

'Invoice Count (Combined)'!$A$2:$A$15000 ...range where country i.e. CA, US etc

B6 = Country i.e. CA, US

'Invoice Count (Combined)'!$AW$2:$AW$15000 ....column where it contains the data i want to obtain, with AW1 being the header

C5 = Purge (type of invoice - i want by, other than country)

'Invoice Count (Combined)'!$D$2:$D$15000 ...type of invoice i.e. purge, offsite etc.


so im trying to get by country i.e CA, by criteria i.e. purge, the value in sheet Invoice Count (Combined) in cell AW.

can someone pls help me with my logic

thx u so much

 

[Marcelo Branco]

If you are using Excel 2007 or higher maybe

=SUMIFS(INDIRECT("'"&A7&"'!AW2:AW15000"),INDIRECT("'"&A7&"'!A2:A15000"),B6,INDIRECT("'"&A7&"'!D2:D15000"),C5)

HTH

M.