New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

SUMIFs formula not functioning

Formulas, Functions, Formatting, Charts, Data Analysis, etc.

SUMIFs formula not functioning

Postby johnkric » Mon Dec 05, 2016 11:18 am

First, anyone and everyone that can help me it is much appreciated. I am a new user here with some level of Excel experience--just not advanced by my standards. I will try to be as descriptive as possible.

Background:
I extract a list of raw data each month from Access that I aggregate into one master Excel file. The excel file is organized by Calendar Reporting month/Platform/etc (see attched). The formula I have in column I is =(SUMIFS(H:H,B:B,C3,F:F,F3,G:G,G3)-SUMIFS(H:H,B:B,E3,G:G,G3)). It works for the first two rows but not after.

Issue:
I would like to create an 'IF' or ‘SUMIF’ function (if possible) that detects the current month’s ISBN count figure (Column "I") that would automatically subtract the prior month’s ISBN count by Role_ID (Column G). The calculation is analogous to a waterfall...I need to automate each prior month variance for both Role ID/

Purpose:
This is intended to easily automate my calculations (current month – prior months) usage in Column I. I am currently doing this manually.

Please see attached file

thanks
  • 0

You do not have the required permissions to view the files attached to this post.
johnkric
Rookie
 
Posts: 2
Joined: Dec 5, 2016
Reputation: 0
Excel Version: 2013

Re: SUMIFs formula not functioning

Postby gebobs » Mon Dec 05, 2016 4:47 pm

You are using a criteria range in the first sumif (B:B) that is wholly unrelated to the criteria (C3). Try changing that. If you are still having problems, let me know.

In the second, you seem to make the same mistake, this time comparing B:B to E3.

I think the range in both is probably intended to be C:C, but the criteria in the second (E3) will not work since it is the month only and does not have a year.
  • 0

gebobs
Regular
 
Posts: 74
Joined: Feb 23, 2015
Reputation: 9
Excel Version: 2010

Re: SUMIFs formula not functioning

Postby gebobs » Mon Dec 05, 2016 4:54 pm

What exactly is the calculation you need to do? Perhaps this could be done with a pivot table.
  • 0

gebobs
Regular
 
Posts: 74
Joined: Feb 23, 2015
Reputation: 9
Excel Version: 2010

Re: SUMIFs formula not functioning

Postby johnkric » Tue Dec 06, 2016 9:20 am

Hi Gebobs - thank you for replying. I am trying to calculate the monthly variances of current month's usage vs. prior month's usage - the calculation/formula is in column "I" - it is taking current month's usage which is in column "H" and subtracting that usage to prior month's usage. I am currently doing this calc manually to each row. I have attached the file. Any help would be much appreciated...Think this is feasible via a Pivot Table?

Thanks
  • 0

johnkric
Rookie
 
Posts: 2
Joined: Dec 5, 2016
Reputation: 0
Excel Version: 2013

Re: SUMIFs formula not functioning

Postby gebobs » Tue Dec 06, 2016 2:43 pm

Did making the changes I mentioned earlier help?

I'm still not clear what you want to do. Tell me specifically what you want the calculation to do.

e.g. (sum column H for all column c = last month and column g = students) minus ...
  • 0

gebobs
Regular
 
Posts: 74
Joined: Feb 23, 2015
Reputation: 9
Excel Version: 2010


Return to General Excel Questions

Who is online

Users browsing this forum: Google [Bot] and 39 guests