SUMIF Statement -- HELP!!!

**sprokop** · 06-12-2013, 05:37 PM

Working with an inventory, I need a formula that will reference column A:A for a particular value, then sum the row that corresponds. Example:

Capture.JPG

So, I think my formual should look something like : =SUMIF(A:A,"Lotion",B:D) - if correct, it should return with a value of 7. HELP!!!!

**scott.s.fower** · 06-12-2013, 07:49 PM

The sum_range argument does not have to be the same size and shape as the range argument. The actual cells that are added are determined by using the upper leftmost cell in the sum_range argument as the beginning cell, and then including cells that correspond in size and shape to the range argument. For example:

If range is | And sum_range is | Then the actual cells are

A1:A5 | B1:B5 | B1:B5
A1:A5 | B1:B3 | B1:B5
A1:B4 | C1:D4 | C1:D4
A1:B4 | C1:C2 | C1:D4

to work around this why not put a sum in column E = SUM($B1:$D1)

then use the sumif($A:$A,"Lotion",$E:$E),

**Haseeb Avarakkan** · 06-12-2013, 08:15 PM

Either,

=SUMPRODUCT((A1:A100="Lotion")*(B1:D100))

Note: Do not use whole column reference like A:A, use like A1:A1000

Or

=SUM(SUMIF(A:A,"Lotion",OFFSET(B1,,{0,1,2},)))

Note: This is a volatile formula. OFFSET & using just B1 in sum_range area.