XL07 Referencing table coloumn values in sumif statements

[steff.sullivan]

Hi- I was hoping somebody would be able to help me with a problem I'm having.

I want to reference a table of events that lists the dates,the event type and benefits.

Currently I have a working sumif that uses dates to retrieve values from a benefit column within a table, however, I need to add an extra criteria that says "reference if the event type is of a certain type" as well as the existing "reference if the dates are the same".

My main issue is how to go about setting criteria on a table column. I can add and AND() function to the criteria section of the SUMIF() but within that I've got a cell reference (for the date) and I'm looking to do something like:
Calendar[Improvement Type]="Natural Search Ranking" only I can't get it to do the referencing- does anybody know how to reference tables within criteria on cell contents?

I'm looking to put it into:
{=C12+SUMIF(Calendar[Date of implementation],and(D5,_________),Calendar[CTR Benefit])}


Thanks in advance,
Steff

 

[RoryA]

The AND function won't work. You need to use the SUMIFS function which allows multiple criteria which you enter the same way you had your original criteria. Instead of your SUMIF syntax:
=SUMIF(criteria_range, criteria, sum_range)
you use:
=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2)

HTH

 

[steff.sullivan]

Thanks for the help with this- I've tried doing the sumifs:
=C12+SUMIFS(Calendar[Cost p.m.],Calendar[Date of implementation],D5,Calendar[Improvement Type],"Natural Search Ranking")
=C12+SUMIFS(Calendar[Cost p.m.],Calendar[Date of implementation],D3,Calendar[Improvement Type],'Calendar'!$C$10)

but it still isn't returning values or returning the value corresponding to calendar!c10
How should I be referencing this as I can't seem to get it to work

Thanks again for your help!

Steff

 

[steff.sullivan]

Thanks for the help

By deleting the row and starting it again with the conditions in a different order it decided to work.

 

[RoryA]

If you try each of the conditions individually, do you get values back for both?