Hi - can someone advise why the following 'sumifs' doesn't work the same way as a normal 'sum', and suggest the correct the syntax to achieve this in the sumifs.
Capture.JPG
Hi - can someone advise why the following 'sumifs' doesn't work the same way as a normal 'sum', and suggest the correct the syntax to achieve this in the sumifs.
Capture.JPG
Are you still using Excel 2010? If not, please update your profile.
You can't do that subtraction from a range in a SUMIFS - the syntax won't allow it. Try putting the subtraction OUTSIDE the SUMIFS.
There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.
A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
I know it doesn't work for the 'sumifs' - that's why I was asking if anyone knew an easy fix or alternative.
Putting the subtraction on the outside does not give the right result. The subtractor needs to be deducted from each cell of the array.
Example workbook now attached.
I am using excel 365, and will update my profile.
Excel query.xlsx
Last edited by jedoob; 03-01-2024 at 10:09 AM.
How aboutFormula:Please Login or Register to view this content.
Now that's what I'm talking about! Thank you!
Glad to help & thanks for the feedback.
Pls try this formula
Formula:Please Login or Register to view this content.
Not better than the solutions you've been given, but, just for fun:
Formula:Please Login or Register to view this content.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
With Excel 365 you can use just SUM.
Formula:Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks