SUMIF with two variables/conditions ?

[behedwin]

Hey

I have a table with 3 columns

COLOR
NUMBER1
NUMBER2

An example can look like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Color
[/TD]
[TD]Number 1
[/TD]
[TD]Number 2
[/TD]
[/TR]
[TR]
[TD]Green
[/TD]
[TD]1
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]Green
[/TD]
[TD]1
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]Red
[/TD]
[TD]2
[/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD]Green
[/TD]
[TD]2
[/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD]Yellow
[/TD]
[TD]3
[/TD]
[TD]30
[/TD]
[/TR]
[TR]
[TD]Red
[/TD]
[TD]3
[/TD]
[TD]30
[/TD]
[/TR]
[TR]
[TD]Green
[/TD]
[TD]4
[/TD]
[TD]10
[/TD]
[/TR]
</tbody>[/TABLE]


Now i want to answer this question.
What is the total sum of Column Number2 with Column COLOR=Green and Column Number1=1

So Green have Two number1 column with the value "1"
So the answer should be 20 (10+10)

I am thinking something like this:
=SUMIF(A2:A8;"Green";B2:B8="1";C2:C8)

Or how should i write this?

 

[Fluff]

How about
=SUMIFS(C2:C8,A2:A8,"Green",B2:B8,1)

 

[behedwin]

How about
=SUMIFS(C2:C8,A2:A8,"Green",B2:B8,1)

Great that worked out just fine.
However i noticed a problem.
In column B where i have numbers i noticed i might also have a letter after the first number.

Can i somehow just read the first number reading from left.
So if B column have
1
2
3d
1
3c
4a
9

it would still just look at the first value in the string?

can i type like "1*" and it would look for 1 and anything that comes after?

 

[Fluff]

Try
=SUMPRODUCT((A2:A8="Green")*(LEFT(B2:B8)="1"),C2:C8)

 

[behedwin]

Try
=SUMPRODUCT((A2:A8="Green")*(LEFT(B2:B8)="1"),C2:C8)

Great that worked

 

[Fluff]

You're welcome & thanks for the feedback