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.

 

[D_excel]

Basically the cell would need to check if the Leave from contains Cananda or USA, then if so does the month correspond with the (Jan, Feb, Mar) so if it meets criteria say Canada, Jan, then it needs to determine it is only the airfare and sum only the airfare for the month of January. While doing this for all the months and all the categories

 

[NBVC]

Let's try this....

Assuming your database (from Post # 5) is in a sheet called "DataBase", and is contained in A1:O10

and assuming the first result for Air Fare in your results sheet lands in I2, use formula:

=SUMPRODUCT(ISNUMBER(SEARCH($A2,DataBase!$B$2:$B$10))*ISNUMBER(SEARCH(I$1,DataBase!$A$2:$A$10))*(DataBase!$D$1:$O$1=TEXT(Sheet3!E2,"mmmm")),DataBase!$D$2:$O$10)

copied across and down, after adjusting ranges to suit.

This assumes exact matches occur in your database... for example Air Fare must occur in column A of Destination sheet as Air Fare (not Airfare).... same for country.

I have also assumed that in the Database you have entered the Months as text strings (not dates formatted as MMMM).

 

[D_excel]

Let's try this....

Assuming your database (from Post # 5) is in a sheet called "DataBase", and is contained in A1:O10

and assuming the first result for Air Fare in your results sheet lands in I2, use formula:

=SUMPRODUCT(ISNUMBER(SEARCH($A2,DataBase!$B$2:$B$10))*ISNUMBER(SEARCH(I$1,DataBase!$A$2:$A$10))*(DataBase!$D$1:$O$1=TEXT(Sheet3!E2,"mmmm")),DataBase!$D$2:$O$10)

copied across and down, after adjusting ranges to suit.

This assumes exact matches occur in your database... for example Air Fare must occur in column A of Destination sheet as Air Fare (not Airfare).... same for country.

I have also assumed that in the Database you have entered the Months as text strings (not dates formatted as MMMM).

Think I am still slightly confused after trying this a few different times. Is there anyway I can show you an example excel to get a better idea of what you mean? Thanks so much for your help btw.

 

[D_excel]

https://www.dropbox.com/s/wj1elt61m1grm8q/Example.xlsx

here is a link to the file which might help a lot more than me fumbling around trying to explain.

 

[NBVC]

In the Data sheet, D2:

=SUMPRODUCT(('Data (2)'!$C$1:$F$1=$A2)*('Data (2)'!$A$2:$A$46=$B2)*(TEXT('Data (2)'!$B$2:$B$46,"mmmm")=D$1),'Data (2)'!$C$2:$F$46)

copied down and across

in the Data (2) sheet, I2:

=SUMPRODUCT(($C$1:$F$1=$H2)*(TEXT('Data (2)'!$B$2:$B$46,"mmm")=I$1),'Data (2)'!$C$2:$F$46)

copied down and across

 

[D_excel]

In the Data sheet, D2:

=SUMPRODUCT(('Data (2)'!$C$1:$F$1=$A2)*('Data (2)'!$A$2:$A$46=$B2)*(TEXT('Data (2)'!$B$2:$B$46,"mmmm")=D$1),'Data (2)'!$C$2:$F$46)

copied down and across

in the Data (2) sheet, I2:

=SUMPRODUCT(($C$1:$F$1=$H2)*(TEXT('Data (2)'!$B$2:$B$46,"mmm")=I$1),'Data (2)'!$C$2:$F$46)

copied down and across

Thanks a ton I owe you a beer or something - this works perfectly!