SUMIFs using multiple criteria or an Array formula

D_excel

New Member
Joined
Jan 7, 2014
Messages
21
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.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
How is your data setup? I.e. are the months across the top horizontally and the Country and Category listed in columns?
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,157
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You may also be able to use a SUM(SUMIFS(...)) construction.

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

D_excel

New Member
Joined
Jan 7, 2014
Messages
21

ADVERTISEMENT

JanuaryFebruaryMarchAprilMayJuneJulyAugust
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

New Member
Joined
Jan 7, 2014
Messages
21
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 FromTo DestinationMODEDAYSMONTHFORMULATOTALAir FareAccomodationTotal Per DiemCar RentalRail FareTaxiOther
CanadaUSA - CentralAir5Feb-13Non-Client_USA - Central_Air2,500.00650.00800.00300.00750.00N/AN/AN/A

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

D_excel

New Member
Joined
Jan 7, 2014
Messages
21

ADVERTISEMENT

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

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
Ok you partly answered the question there... So as long as the 2nd column in your database contains "Canada" you want to summarize?
 

Forum statistics

Threads
1,137,060
Messages
5,679,380
Members
419,824
Latest member
Mercy kiara

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top