SUMIF for a wider range

Question

I have a problem that has been nagging me for a while now. I am a novice in Excel. Hence excuse me if this comes across as silly.

I have the following array of data:

I need to obtain the sum of values corresponding to the row entry "book". I have used the formula sumif(B4:F7,"book",C4:F7). However excel returns the value in column c alone (i.e value returned is 10).

Update

I ammended my formula as follows:

I now obtained the correct sum - i.e 70. However, I deal with large volumes of data and using this formula is not efficient. Can anyone suggest a quick workaround for this?

Answer 1

Your guess is correct. There is a simpler way, but it is not easy to find it out for a novice user. You will need to use an array formula to do what you need.

Type this formula:

=SUM(IF($B4:$B7="book",$C$4:$F$7,0))

and enter it using Ctrl + Shift + Enter instead of just Enter

You can see the result in this picture (my delimiter is ; while yours will be ,, but that does not make a difference):