SUMIFs using multiple criteria or an Array formula

[D_excel]

Hello I am trying to do a sum of data that contains three requirements - The country has to match either the USA or Canada and then it needs to pull for only a certain month (i.e. Jan, Feb, Mar) and then for that month only pull either Airfare, Hotel, Per Diem, or Car Rental. Basically looking for a way to Sum only the specific category for a specific time frame. Any help would be greatly appreciated.

 

[NBVC]

How is your data setup? I.e. are the months across the top horizontally and the Country and Category listed in columns?

 

[NBVC]

If so,

Try:

=SUMPRODUCT(ISNUMBER(MATCH($A$2:$A$6,{"Canada","Usa"},0))*ISNUMBER(MATCH($B$2:$B$6,{"Airfare","Hotel","Per Diem","Car Rental"},0))*($C$1:$N$1="Jun"),$C$2:$N$6)

assuming Countries in column A, Categories in Column B, and months in Row 1, starting at C1.

adjust ranges to suit.

 

[RoryA]

You may also be able to use a SUM(SUMIFS(...)) construction.

OT: Hi, V - nice to see you in these parts!

 

[D_excel]

			January	February	March	April	May	June	July	August
831105 - Airfare - Internal	[10] - United States	[040] - Shared Svcs-G&A HR Svcs
831205 - Hotels - Internal	[10] - United States	[040] - Shared Svcs-G&A HR Svcs
831305 - Meals & Entertainment - Internal	[10] - United States	[040] - Shared Svcs-G&A HR Svcs
831500 - Rental Car/ Ground Transportation	[10] - United States	[040] - Shared Svcs-G&A HR Svcs

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col span="4"><col></colgroup>

 

[D_excel]

so the above is how the table that I want the data to feed into is set up.

Here is an example of the data it is pulling from:

Leave From

To Destination

MODE

DAYS

MONTH

FORMULA

TOTAL

Air Fare

Accomodation

Total Per Diem

Car Rental

Rail Fare

Taxi

Other

Canada

USA - Central

Air

5

Feb-13

Non-Client_USA - Central_Air

2,500.00

650.00

800.00

300.00

750.00

N/A

N/A

N/A

<tbody>
</tbody><colgroup><col span="2"><col><col><col><col><col><col span="8"></colgroup>

 

[D_excel]

The Leave from is the trigger for the country, Airfare Hotel Per Diem and Car Rental are in a row instead of a column.

 

[NBVC]

Not sure of how you suggest the match up for country? Are you looking at the Leave From or To Destination column? Can you explain, in words, what the formula would be doing to get, say your Air Fare figure for your above line?


P.s. Hey Rory

 

[NBVC]

Ok you partly answered the question there... So as long as the 2nd column in your database contains "Canada" you want to summarize?

 

[D_excel]

Ok you partly answered the question there... So as long as the 2nd column in your database contains "Canada" you want to summarize?

Yes this is correct.