Multiple Criteria Sumif

[vjimen2]

Hello Everyone,

I'm stuck on a formula and trying to get it to work correctly. I'm trying use a formula that will look at two different criteria, one in a column and one in a row. Once it finds both criteria it will pull back a certain cell value where the two meet. The data is very large and has about 5000 rows. Also, both of the criteria can be changed through a drop down box to meet certain needs of the business.

EX. Cell A1 has "YTD" but can change to Q1 or Q2...

Cell A3 has "Store Payables" and can change to Outside service, Labor....

In a very large range of data i need the formula to find where the two cells meet on the axis and pull back that value.

And one more thing, the "Store Payables" is a Nickname for the real value that is going to be in the range of cells to look for that value. I've already created a table where a vlookup could be used to pull the real name of the value to be found.

Thank you for any help in solving this one!


EDIT:
=SUMIF('Input - Essbase'!$C$60:$C$4809,VLOOKUP(B23,'Account Index'!$C$39:$D$71,2),'Input - Essbase'!$H$60:$S$4809)/1000000

This is the formula I've been trying to use but it will not pull back the correct amount and it is also only looking at one criteria. I tried using an "AND("function but had no luck.

 

[vjimen2]

Re: Mulitple Criteria Sumif

=SUMIFS('Input - Essbase'!$C$59:$X$4809,'Input - Essbase'!$H$59:$X$59,'Exec - Summary P&L Totals'!$B$3,'Input - Essbase'!$C$590:$C$4809,VLOOKUP('Exec - Summary P&L Totals'!B23,'Account Index'!$C$39:$D$71,2))/1000000

I also tried this but it gives me a #value error.

 

[Joe4]

Re: Mulitple Criteria Sumif

Your Criteria ranges need to be the same size.
You have some starting at C59 and others starting at C590.
I assume that is just a typo.

 

[vjimen2]

Re: Mulitple Criteria Sumif

Thanks for finding that, it was a typo. I corrected that part in the formula and still get the #value error. Do the criteria ranges need to include the sum range? So instead of C59:C4809 it should be C59:X4809?

 

[Joe4]

Re: Mulitple Criteria Sumif

See: https://www.ablebits.com/office-addins-blog/2014/11/12/excel-sumifs-sumif-multiple-criteria/
And note this statement:

[FONT=&quot]In Excel SUMIFS, each criteria_range must contain the same number of rows and columns as the sum_range parameter.[/FONT]

That means all your Criteria ranges and Sum range must all be the same size (each must have the same number of rows and columns).

 

[vjimen2]

Re: Mulitple Criteria Sumif

Thanks! That started pulling back a number. Unfortunately that number is zero. Not sure why, but at least its pulling back something now.

 

[Marcelo Branco]

Re: Mulitple Criteria Sumif

Thanks! That started pulling back a number. Unfortunately that number is zero. Not sure why, but at least its pulling back something now.

Check what is being returned by
VLOOKUP('Exec - Summary P&L Totals'!B23,'Account Index'!$C$39:$D$71,2)

If you need an exact match, try
VLOOKUP('Exec - Summary P&L Totals'!B23,'Account Index'!$C$39:$D$71,2,0)

M.

 

[vjimen2]

Re: Mulitple Criteria Sumif

Thanks Marcelo, the vlookup needed the 0 at the end to pull an exact match. After correcting this it still doesn't pull back the correct value, just a zero.

Objective is to make this find a value in the row, find a value in the column and then bring back the cell value where they meet. Like a multiplication table, you find 9 and 9 and the cell equals 81. Is there an easier way to make that happen in excel other than using sumifs?

 

[vjimen2]

Re: Mulitple Criteria Sumif

=INDEX('Input - Essbase'!$C$59:$X$4809,MATCH(VLOOKUP(B24,'Account Index'!$C$39:$D$71,2,0),'Input - Essbase'!$C$59:$C$4809,0),MATCH('Exec - Summary P&L Totals'!$B$3,'Input - Essbase'!$C$59:$X$59,0))/1000000

This formula got it to work! Thanks everyone for the help.