x
Solved

# Revenue and cost calculations based on timing of rollout criteria Part II

Posted on 2014-04-18
Medium Priority
278 Views
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28413405.html#a40009686

Continuation of Part I referenced above.

I need to be able to provide a start and end month for each scenario presented.
Monthly-Model-Example.xlsx
0
Question by:mdg1
• 6
• 2

LVL 19

Accepted Solution

Ken Butters earned 2000 total points
ID: 40009801
Not sure if I hit all your cells that need updating or not...

I added Row 12 which defines an ending month...
then updated Row 27 "Monthly revenue" to check both start and end month.

The way sumifs works is sumifs(sum_range,criteria_range1,criteria1,critieria_range2,criteria2,...etc)

you can have as many pairs of criteria_range and criteria as you want.

in this posted example in cell I27 I have this formula:
=SUMIFS(\$B\$27:\$H\$27,\$B\$11:\$H11,"<= 1",\$B\$12:\$H\$12,">=1")

That means that we add B27 through H27...

But only if BOTH of the following critiera are true:

1) if B11 through H11 <= 1
2) If B12 through H12 >= 1

B11 through H11 are startiong month... and in Cell I27 is for month 1... so we want to add it if the starting month is <= 1 AND if the ending month >= 1.

Same logic applies to other cells as needed throughout the workbook.
Monthly-Model-Example.xlsx
0

Author Comment

ID: 40009806
Looks good!  I'll continue to play with it and post comments or accept this by Monday.  Thanks so much.  Have a great weekend.  Cheers!
0

Author Comment

ID: 40013617

I will be posting another question related to this series.  I'll paste the url once I post the question.
0

LVL 19

Expert Comment

ID: 40013731
Reminder to close this question and award points if the answer is correct.   :)
0

Author Comment

ID: 40013743
I've requested that this question be closed as follows:

Accepted answer: 0 points for mdg1's comment #a40009806

for the following reason:

Solution worked great and was delivered very quickly.
0

Author Closing Comment

ID: 40013744
excellent
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.