SOLVED

Sumif with Relative reference

%3CLINGO-SUB%20id%3D%22%5C%26quot%3Blingo-sub-3134688%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3ESumif%20with%20Relative%20reference%26lt%3B%5C%2Flingo-sub%26gt%3B%3CLINGO-BODY%20id%3D%22%5C%26quot%3Blingo-body-3134688%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CP%3EHi%2C%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EI%20want%20to%20sum%20a%20column%20and%20sum%20only%20cells%20that%20the%20cell%20in%20the%20next%20column%20is%20equal%20to.%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%5C%26quot%3Blia-inline-image-display-wrapper%22%20lia-image-align-inline%3D%22%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Fgxcuf89792%2F%5C%26quot%3Bhttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F346103i04EC9B1672BC58C5%2Fimage-dimensions%2F219x131%3Fv%3Dv2%5C%26quot%3B%22%20width%3D%22%5C%26quot%3B219%5C%26quot%3B%22%20height%3D%22%5C%26quot%3B131%5C%26quot%3B%22%20role%3D%22%5C%26quot%3Bbutton%5C%26quot%3B%22%20title%3D%22Hadas365_0-1644298009368.png%22%20alt%3D%22%5C%26quot%3BHadas365_0-1644298009368.png%5C%26quot%3B%22%20%2F%3E%26lt%3B%5C%2Fspan%26gt%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Eit%20shold%20give%20in%20this%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%26lt%3B%5C%2Flingo-body%26gt%3B%3CLINGO-LABS%20id%3D%22%5C%26quot%3Blingo-labs-3134688%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CLINGO-LABEL%3EExcel%26lt%3B%5C%2Flingo-label%26gt%3B%26lt%3B%5C%2Flingo-labs%26gt%3B%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3134688%22%20slang%3D%22en-US%22%3ESumif%20with%20Relative%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3134688%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20sum%20a%20column%20and%20sum%20only%20cells%20that%20the%20cell%20in%20the%20next%20column%20is%20equal%20to.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Hadas365_0-1644298009368.png%22%20style%3D%22width%3A%20219px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F346103i04EC9B1672BC58C5%2Fimage-dimensions%2F219x131%3Fv%3Dv2%22%20width%3D%22219%22%20height%3D%22131%22%20role%3D%22button%22%20title%3D%22Hadas365_0-1644298009368.png%22%20alt%3D%22Hadas365_0-1644298009368.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Eit%20shold%20give%20in%20this%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3134688%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

Hi,

 

I want to sum a column and sum only cells that the cell in the next column is equal to.

Hadas365_0-1644298009368.png

it shold give in this 

 

 

 

 

4 Replies

@Hadas365 You didn't finish the question, but I suspect you need something like this:

Screenshot 2022-02-08 at 06.49.04.png

SUMPRODUCT will work in all Excel versions. FILTER works in recent Excel versions only.

I have attached a file containing the examples in the picture.

HI,
thanks. the filter works great.
but if i want to add additional term?
best response confirmed by Hadas365 (New Contributor)
Solution

@Hadas365 More about using multiple criteria with FILTER in the attached link.

https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759 

it works!
thanks!