SumIf

[TobiManobi]

Dear all,

I have a table with 4 colums. If the arguments in the first 3 columns are met, I would like to see the sum of the 4th column.

Here an Example:
---------A -----B ------C ------D
------ Type ----- Year ----- Color ----- Value
1------ A ----- 2010 ----- red -------- 10
2------ B ----- 2010 ----- blue ------- 11
3------ A ----- 2011 ----- green ------ 12
4------ B ----- 2011 ----- red -------- 13

The list is quite long, but I will always have only one match/ that corresponds to the three arguments.

So far, I tried the sum formula:

=SUM((A1:A4="A")*(B1:B4="2010")*(C1:C4="red")*(D1:D4))

But instead of 10, the value I get is always 0
With two arguments, this formula works perfect.

I guess either it does not work with 3 arguments, or it has problems with the 'year' column as it might have problems with numbers instead of letter??

Any idea how I could solve it???

Thanks a lot!

[teylyn]

What is the data type in the Year column? If it is a number, change your formula to

=SUM((A1:A4="A")*(B1:B4=2010)*(C1:C4="red")*(D1:D4))

you could also use Sumproduct, which you can enter normally, no CSE, something like

=SUMPRODUCT((A1:A4="A")*(B1:B4=2010)*(C1:C4="red")*(D1:D4))

[quekbc]

Hi Tobi,

The formula works for me using

Please Login or Register  to view this content.

The only difference is that i use B1:B4=2010 rather than B1:B4 = "2010"

[TobiManobi]

Thanks a lot! Without the quotation marks for 2010 it works!

[TobiManobi]

Ok that works but I have a small modification question.

Let's say instead of type 'A' and 'B' in column A, we have different types for example AAA, ABB ABA, BAA, BBB.

Can we modify the formular, so that excel gives me the sum of all types STARTING with letter A??

=SUM((A1:A4="A")*(B1:B4="2010")*(C1:C4="red")*(D1:D4))

Thanks a lot in advance!!

[quekbc]

You could probably use the LEFT function. How it works is that it takes the left # characters from a string.

Example,
=LEFT("AAA",1) will give you "A"
=LEFT("AAA",2) will give you "AA"

So, in your case, you would modify your code to be....

Please Login or Register  to view this content.

[TobiManobi]

Great! Thank you very much!!

[teylyn]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save