help with SUMIFSs for my 1rst dashboard

[mario rdz]

Hello All..

I'm re-designing some monthly reports, what im doing for the 1rst is a kind of dash board to show the purchase expense by Business & material groups, for the last 3 years.

I have included several PTs,, wich not sure if it is the best way, since the total data will be around 200,000 rows...

My problem is that I want to summarize the expense and then splited into years in one table, then splited in years-mat. groups... Im using the next formula :

=SUMIFS(INDIRECT("DataByBsn!H2:H" &lrBsn),INDIRECT("DataByBsn!D2:D" &lrBsn),J3,INDIRECT("DataByBsn!C2:C" &lrBsn),R2)

lrBsn contains the last row of sheet DataBYbsn

But when I add one more criteria in SUMIFS, the formula shows cero

=SUMIFS(INDIRECT("DataByBsn!H2:H" &lrBsn),INDIRECT("DataByBsn!D2:D" &lrBsn),J3,INDIRECT("DataByBsn!C2:C" &lrBsn),R2,INDIRECT("DataByBsn!C2:C" &lrBsn),R4)

H2:H" &lrBsn = is the range to sum the $$
J3 = sum year 2014
C2:C" &lrBsn = is the range for business (cement, reeady mix, transport, etc)
R2 = Business Cement
R4 = Business ReadyMix

If does not find any $$ for Ready Mix,, should give me the sum for Cement at least,, but shows CERO

Any help will be really appreciated...

Mario

 

[Hui]

Mario

Are you able to post the file for us to review?

 

[mario rdz]

Dear Hui..

Thanks for your replay and sorry for my late response..
I was dealing with the report during the weekend, and found out that SUMPRODUCT formula will be an easer and faster way to work with..

Here is a pic from my advance in the construction of the dashboard, let me finish it and then I will upload it to see any comment..

Regards