SUMIF with multiple column and row criteria

Hi All,

I am looking for some help with a formula for the attached spreadsheet's summary Tab (G5:K6).

Demo1.xlsx

The formula needs to be able to read from the Data Tab and:

1. Total the values in a column based on the country being selected and then multiple expense types being selected by dropdown (up to 3). Formula in G9 does part of this (via help on the forum yesterday).
2. By changing the year and Month in rows 2 and 3 the summary table should update accordingly.

I am trying not to use named ranges for the columns and think a sumif/index/match formula may be the way to go but I have tried so many different things that are not working I think my head is about to pop.

Any help is greatly appreciated.

Thanks,

Adam

May be like this:

Please Login or Register  to view this content.

Hi, Adam.

1st I want to know how you want the report looks like? upload the sample, if need adjustment, i will tell you.

Not sure if you got your answer yet, but try this in G5, copied down and across...
=SUMPRODUCT((Data!$A$5:$A$17=$C5)*((Data!$B$5:$B$17=$D5)+(Data!$B$5:$B$17=$E5)+(Data!$B$5:$B$17=$F5))*(Data!$D$1:$M$1=G$2)*(Data!$D$2:$M$2=G$3)*(Data!$D$3:$M$3=G$4),Data!$D$5:$M$17)

For qtr and year, it would probably be easier if you used read dates instead of just years and names...1/1/14, 2/1/14 etc

Hi, Adam



As FDibbinss suggest "For qtr and year, it would probably be easier if you used read dates instead of just years and names...1/1/14, 2/1/14 etc ".

I make a little adjusment for you.

See the attachment.

Hi David,

Please see updated Attachment.

Demo2.xlsx

Cells N2:N6 show what I hope the Qtr1 report would look like and it is the combined totals of Jan,Feb,Mar

Qtr2 would be Apr,May,Jun
Qtr3 would be Jul,Aug,Sep
Qtr4 would be Oct,Nov,Dec

In the report I have summed the 3 months to get the total Qtr1 but would like this to be part of the formula as I would normally only want to show the 4 Qtr's per year in the report.

Also there is the potential that there may be only a partial Qtr i.e only one or two months in the data as per the data in I2:I6.

I hope that makes sense.

Thanks and Regards,

Adam

Hi, Adam

Check my post #9, with that you can create the formula, you just need make a little adjustment.

I don't know your problem solved or not, but if you insist using text for month and year, i give you another option for summary.

See the attachment.

I have changed your dates to real dates, and modified the formulas to pull in the month, the QTR and the YTD

If you want to include all countries, then just remove the test for the country....
(Data!$A$6:$A$18=$C5)
take that part out

I am out of time right now, I will take a look again soon

Great stuff. Can you share the formula with us?

Impressive

Thanks Ford

Formula is below with Named Ranges for fixed data columns and rows. It is a little shorter.

=Round(SUMPRODUCT(((If($I6="All",1,Country=$I6))*If($J6="All",1,((Expense=$J6)+(Expense=$K6)+(Expense=$L6)+(Expense=$M6)+(Expense=$N6)))*(Year=Q$3)*(If(Isnumber(Q$4), Quarter=Q$4,Month_=Q$4)))*Budget),0)

Cheers,

Adam

Looks good Keep in mind that when working with dates, its almost always better to use actual dates, rather than names and years.

In the WB I uploaded, I forgot to mention that I used ONLY the dates in the top row (1), the other 2 (year) and Month) were not used at all