CODEWITHSUNDEEP
excelexcel-formularangeworksheet-functionsumifs
nebu varghese
nebu varghese

Reputation: 11

SUMIF for a wider range

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:
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:
Ammended Formula

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?

Upvotes: 0

Views: 2867

Answers (1)

ZygD
ZygD

Reputation: 24508

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):
enter image description here

Upvotes: 1

Related Questions