SUMIF/SUMIFS based on criteria

Jessintha Nathan

Hi!

How do I sum a specific category (Primary Column) with reference to a separate sheet. I keep getting multiple errors:

Debbie Sawyer

HI Jessintha

I'd be happy to help, but would need a little more information about the column that you are wanting to SUM and the condition that needs to meet true inorder for the value to be relevant to the function.

When you start a function as soon as you have type the =FUNCTIONNAME( bit a helper tool opens up on your screen. To reference another sheet click the blue link in the helper and a linking dialog box opens up enabling you to select the range required.

If you let us know which columns value you want to add and the condition that needs to be met, I'm sure you'll get your function really quickly :)

Kind regards

​Debbie Sawyer Consultant & Training Manager

 

Jessintha Nathan

Hi Debbie. I'm looking to sum in column c84 by looking up primary column in a separate sheet for said period.

Debbie Sawyer

Thank you

I'm still not certain which cell you are referring to by c84 - is that [Primary Column]84 or [Column4]84?

Whichever... try using

=SUMIFS([Column to SUM]:[Column to SUM],{Start Date Range},>=[Start Date]@row,{End Date Range},<=[End Date]@row)

Would this work? I'm still not 100% certain on the requirement! sorry

SUMIFS is the function to use to total values based on more than 1 criteria meeting true. { } surround ranges of cells from other sheets and are entered into a formula from using the Blue Link Reference tool from within the formula helper, [ ] Column names typically have square brackets around them (if they contain a space or end in a number). @row will take the value from the row that the formula result is being entered into.

I hope these pointers will help you find your resolution.

Feel free to come back if you think I can help futher.

Kind regards

Debbie

Jessintha Nathan

The formula returned unparseble. I'm trying to Sum Value in Primary Column 84 from another sheet.

=SUMIFS({Weekly Shop Visit Update Range 41}:{Weekly Shop Visit Update Range 41},{Weekly Shop Visit Update Range 1},>=[Start Date]84@row,{Weekly Shop Visit Update 31},<=[End Date]84@row, {Weekly Shop Visit Update Range 39}, [Primary Column]84)

Debbie Sawyer

Hi

The [Start Date]84@row will be causing an issue. You either need it to be the @row statement OR use a row number like 84...

Change the references that use both to either the number or the @row...

i.e. use either:

=SUMIFS({Weekly Shop Visit Update Range 41}:{Weekly Shop Visit Update Range 41},{Weekly Shop Visit Update Range 1},>=[Start Date]@row,{Weekly Shop Visit Update 31},<=[End Date]@row, {Weekly Shop Visit Update Range 39}, [Primary Column]84)

or

=SUMIFS({Weekly Shop Visit Update Range 41}:{Weekly Shop Visit Update Range 41},{Weekly Shop Visit Update Range 1},>=[Start Date]@row,{Weekly Shop Visit Update 31},<=[End Date]@row, {Weekly Shop Visit Update Range 39}, [Primary Column]84)

Do either of these work?

Jessintha Nathan

hi Debbie

I tried both and using row number with error #unparseable

=SUMIFS({Weekly Shop Visit Update Range 41}:{Weekly Shop Visit Update Range 41},{Weekly Shop Visit Update Range 1},>=[Start Date]84,{Weekly Shop Visit Update 31},<=[End Date]84, {Weekly Shop Visit Update Range 39}, [Primary Column]84)

Debbie Sawyer

Hi @Jessintha Nathan

Can you confirm if the Bold Italic area is a range of cells or just one cell...

=SUMIFS({Weekly Shop Visit Update Range 41}:{Weekly Shop Visit Update Range 41},{Weekly Shop Visit Update Range 1},>=[Start Date]84,{Weekly Shop Visit Update 31},<=[End Date]84, {Weekly Shop Visit Update Range 39}, [Primary Column]84)

If it is a range - check your range to ensure that it is set up in the same way as your other 3 ranges in this formula. If it is a cell re-write it like this [Weekly Shop Visit Update]31

So tricky to trouble shoot when you can't see the sheets! :)

Good luck

Kind regards

Debbie