CODEWITHSUNDEEP
excelsyntaxconditional-statementssumifs
swiz
swiz

Reputation: 89

Excel SUMIFS with OR, IF, and multiple criteria

I am having a problem using SUMIFS. I have columns cost, frequency, interval, and month, where frequency is a string ("monthly", "annually", "other", "once") and the others are all numerical.

I am trying to do a SUMIFS where I sum costs depending on different values of frequency, and have something like this:

=SUMIFS(cost, freq, "="&OR("monthly", IF(mod(month,12)=interval,"annually"), IF(mod(month,interval) = 0, "other"), IF(month=interval, "once"))

In other words, if freq=monthly, sum; if freq=annually, sum if mod is true; if freq=other, sum if mod is true; and if freq=once, sum if month=interval.

Thanks!

Upvotes: 0

Views: 610

Answers (1)

ExactaBox
ExactaBox

Reputation: 3395

You will be better off creating an extra column with a formula containing all the logic and simply returning 1 or 0, then using that as the basis of the SUM. Something like:

=IF(OR(freq = "monthly" , AND(freq = "annually", mod = true) , AND(freq = "other", mod = true), AND(freq = "once", month = "interval")), 1, 0)

Upvotes: 2

Related Questions