# Can you use SUMIF for 2-columns of criteria ?

1. ## Can you use SUMIF for 2-columns of criteria ?

Using 2-criterias, how do I extract a column value from a 3rd

column?

On sheet_#1 I am trying to obtain a value from sheet_#2 where

the value of interest is in the 3rd column, and only extract

the value if the 1st and 2nd column meet specific criteria

(example shown below).

I have tried using SUMIF but I am unsuccessful in developing

the formula for the 2nd critera, can someone assist ?

example of intent of logic in a cell in sheet_#1
=sumif(A1:C10,("BLue" AND "Low"),C1:c10)

sheet_#2 columns

"A "B" "C"

row1 Blue High 2
row2 Blue Low 12
row3 Blue Mid 6
row4 Blue Closed 7
,,,
,,,

Thanks in advance for you help

jay

2. Hi

Try something like

=sumproduct(--(a1:a10="Blue),--(b1:b10="High"),(c1:c10))

rylo

3. ## Your example works ! ... But what do the double 'dash' mean

Hi Rylo,

Your a genius, and "Thank you" very much. Your SUMPRODUCT example does the task, however, I am not fimiliar with what the double dashes in the formula do.

Could you educate me ?

sumproduct(dash dash(a1:a10="Blue),dash dash(b1:b10="High"),(c1:c10))

from your

sumproduct(--(a1:a10="Blue),--(b1:b10="High"),(c1:c10))

Thanks again

jay

4. ## If I may jump in...

The double dashes are two negative signs.

(a1:a10="Blue") results in an array of TRUE or FALSE values, and you need to convert that to an array of 0s and 1s to be able to multiply it with your array of values (the values in your third column). Sumproduct multiplies the elements of the arrays you put into it and sums the result.

You can convert TRUE or FALSE to 0s and 1s with various methods, the double negative sign is one of them. If you only used one negative sign, you would have an array of 0s and -1s.

Brigitte

5. ## I'm wow'ed agian :-))

Thank you Brigitte (and again Rylo) for providing some new insight. Its great to learn something new everyday :-)

Jay

##### Users Browsing this Thread

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