# SUMIF based on the multiple criteria

1. ## SUMIF based on the multiple criteria

Hi all,

I'm trying to sum specific columns of table based on the values of 2 other cells.

This is basically what I'm trying to accomplish:

IF(\$D\$1="LOW"), =SUMIF(DATA[BR],L19,DATA[PRICELOW])
IF(\$D\$1="MEDIUM"), =SUMIF(DATA[BR],L19,DATA[PRICEMED])
IF(\$D\$1="HIGH"), =SUMIF(DATA[BR],L19,DATA[PRICEHIGH])

Is there a way to put this all into one formula?

Thanks!

2. ## Re: SUMIF based on the multiple criteria

I can't tell what you are trying to accomplish but usually you write a sumif to handle the if then sum. So like =SUMIF(DATA[BR],"LOW",[RANGE TO SUM]) or =SUMIF(DATA[BR],\$D\$1,[RANGE TO SUM])
and the last one will allow you to use D1 as what you need in one formula.

3. ## Re: SUMIF based on the multiple criteria

Dunno if this will work, try it
Assuming D1 can only be LOW MEDIUM or HIGH and nothing else

=SUMIF(DATA([BR],L19,DATA[INDIRECT("PRICE"&\$D\$1])

4. ## Re: SUMIF based on the multiple criteria

Try

=IF(\$D\$1="LOW",SUMIF(DATA[BR],L19,DATA[PRICELOW]),IF(\$D\$1="MEDIUM",SUMIF(DATA[BR],L19,DATA[PRICEMED]),SUMIF(DATA[BR],L19,DATA[PRICEHIGH])))

assumes only these 3 conditions

5. ## Re: SUMIF based on the multiple criteria

I have a combo box with "LOW","MEDIUM", AND "HIGH" which feeds to cell \$D\$1. I have a separate tab that houses my data. If \$D\$1 says "LOW", I want excel to lookup the BR column on my table, find the BR criteria (see below), and provide a SUM of the PRICELOW column for that specific unit type

Unit Type
1BR
2BR
3BR
4BR
5BR
7BR

6. ## Re: SUMIF based on the multiple criteria

That worked perfect John. THANKS!

7. ## Re: SUMIF based on the multiple criteria

my next question would be is how do write a MIN and MAX formula for the Price column that is linked to "LOW,"MEDIUM","HIGH" columns?

8. ## Re: SUMIF based on the multiple criteria

No problem: thanks for the feedback.

9. ## Re: SUMIF based on the multiple criteria

Jumped the gun! Not sure what you mean by MIN/MAX related to the columns.

=MAX(Data[PRICELOW]) ?

10. ## Re: SUMIF based on the multiple criteria

Looking for the lowest/highest price in the PRICELOW column if \$D\$1="LOW", Looking for the lowest/highest price in the PRICEMED column if \$D\$1="MEDIUM",Looking for the lowest/highest price in the PRICEHIGH column if \$D\$1="HIGH"

11. ## Re: SUMIF based on the multiple criteria

Sorry, and for the specific BR Type as well

12. ## Re: SUMIF based on the multiple criteria

Something like ..??

=IF(\$D\$1="LOW",MIN(DATA[PRICELOW]),IF(\$D\$1="MEDIUM",MIN(DATA[PRICEMED]),MIN(DATA[PRICEHIGH])))

Replace MIN with MAX

13. ## Re: SUMIF based on the multiple criteria

Basically looking for the MIN/MAX of this sum formula you gave me

=IF(\$D\$1="LOW",SUMIF(DATA[BR],L19,DATA[PRICELOW]),IF(\$D\$1="MEDIUM",SUMIF(DATA[BR],L19,DATA[PRICEMED]),SUMIF(DATA[BR],L19,DATA[PRICEHIGH])))

14. ## Re: SUMIF based on the multiple criteria

You will need to use INDEX to identify the column of data

Could you post a sample showing expected results as it easier to work with some data.

15. ## Re: SUMIF based on the multiple criteria

I'm able to use this formula to look up the size of the unit because that is not effected by the Low,Med,High criteria
=MIN(IF(DATA[BR]=F98,DATA[SF]))

Price Range SF Range \$PSF Range
\$0 \$0 450 956 \$0 \$0
\$0 \$0 1,422 2,170 \$0 \$0
\$0 \$0 1,857 2,466 \$0 \$0
\$0 \$0 2,193 3,901 \$0 \$0
\$0 \$0 2,534 4,479 \$0 \$0
\$0 \$0 4,463 5,087 \$0 \$0

16. ## Re: SUMIF based on the multiple criteria

If there are other criteria, try

=MIN(IF((DATA[BR]=F98)*(criteria2 ),DATA[SF]))

the "*" acts as an "AND" condition i.e. both are true

or

=MIN(IF(DATA[BR]=F98,IF(criteria2,DATA[SF]),""),"")

17. ## Re: SUMIF based on the multiple criteria

cant get to work

18. ## Re: SUMIF based on the multiple criteria

Please post a sample file: without seeing the data we are working in the dark.

19. ## Re: SUMIF based on the multiple criteria

File attached

20. ## Re: SUMIF based on the multiple criteria

Formula used

=MIN(IF(DATA!\$D\$2:\$D\$89=STACK!\$D4,INDEX(DATA!\$A\$2:\$S\$89,,MATCH("PRICE"&A\$1,DATA!\$A\$1:\$S\$1,0))))

Entered with Ctrl+ShifT+Enter

See attached

21. ## Re: SUMIF based on the multiple criteria

works perfect, Thanks!

22. ## Re: SUMIF based on the multiple criteria

Thank you.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1