• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Shortening a SUMIF function|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Shortening a SUMIF function|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Shortening a SUMIF function
sp_PrintTopic sp_TopicIcon
Shortening a SUMIF function
Avatar
Maria Borrill

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
May 13, 2020
sp_UserOfflineSmall Offline
1
May 13, 2020 - 11:38 pm
sp_Permalink sp_Print

Hi

I would class my self as a novice/ intermediate user - go easy 🙂 

I've created a spreadsheet Excel 2016 to make stores ordering easier, basically the rows list the prices from suppliers the columns add up the CHEAPEST option and SUMIF calculates total cost turns green when min p&p charges reached.

The SUMIF formula is rather long and cumbersome to write out and if a line deleted have to go and correct refs in formula.

I've tried SUMPRODUCT however not 100% successful as things go awry when multiple items ordered.

Can anyone help with a better formula?

Many thanks

sp_AnswersTopicSeeAnswer See Answer
Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 412
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
2
May 14, 2020 - 3:54 am
sp_Permalink sp_Print

Mi Maria

It looks like row 11 has been duplicated in your sumsif in row 14. But then the sum product you had was doubling the first 3 rows which was the difference between the very long sumif and the sumproduct and i have no idea why (i am sure someone on here will know).

However i dont think you need sumif inside the sumproduct - the sumproduct does the sum if.

I have updated your sumproduct to =SUMPRODUCT(--(C$2:C$12=$K$2:$K$12)*$K$2:$K$12) -
the first part is the test c2=k2, c3=k3 so you get trues & falses
the -- (double minus) infront of it turns the trues and falses into 1's and 0's
then the last part multiplies the 1's by the number in column K (2.45*1=2.45,2.45*1=2.45,2.45*1=2.45,10.72*1=10.72,0*49.60,0*23.80,0*32.95,0*10.10,0*2.41,0*4.58,0*5.50)
and then its summed up

(Sorry had to delete the hyperlinks)

Purfleet

sp_AnswersTopicAnswer
Answers Post
Avatar
Maria Borrill

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
May 13, 2020
sp_UserOfflineSmall Offline
3
May 14, 2020 - 6:44 pm
sp_Permalink sp_Print

Many thanks Purfleet I copied and pasted the formula it works great,

Yeah those long Sumif strings it was so easy to miss duplicated ones.

I need my eyes testing  [sod the hair cut] and working on the remote desktop from home on a laptop is making everything smaller to see too, oh the joys of lock down, you've saved me hours of work I've been trying to solve/simplify this for a long time!

Now back to hyperlinking the entire stock list - it's actually several 100 rows long, but it saves me so much time & Science department's budget it's been an ongoing & improving project for many years and as i can't mix chemicals at home need to occupy my time with other projects.

Thanks again!

Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 622
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
4
May 14, 2020 - 8:52 pm
sp_Permalink sp_Print sp_EditHistory

The issue with the SUMPRODUCT and SUMIF construction is that you're basically doing this:
SUMIF(E2:E12,K2)+SUMIF(E2:E12,K3)+SUMIF(E2:E12,K4)+...
and so on.
Since K2:K4 all have the same value, you are tripling the result you actually want.

Also, FYI, you don't need both -- and * in your SUMPRODUCT formula, since both will coerce TRUE/FALSE to 1/0; it doesn't actually need array-entry either.

The following users say thank you to Velouria for this useful post:

Purfleet
Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 412
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
5
May 14, 2020 - 8:56 pm
sp_Permalink sp_Print

I think the -- & * are a force of habit, but fair point.

I assumed the duplication was somthing like that but it didnt seem to happen on anything other than the first 3 rows

Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 622
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
6
May 14, 2020 - 9:56 pm
sp_Permalink sp_Print

That's because they're the only ones with repeated values in column K.

Avatar
Maria Borrill

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
May 13, 2020
sp_UserOfflineSmall Offline
7
May 14, 2020 - 10:03 pm
sp_Permalink sp_Print

Does that mean I can shorten the formula, loseeverything after the * ?

Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 622
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
8
May 14, 2020 - 10:39 pm
sp_Permalink sp_Print

No, it just means that you only need:

=SUMPRODUCT((C$2:C$12=$K$2:$K$12)*$K$2:$K$12)

rather than:

=SUMPRODUCT(--(C$2:C$12=$K$2:$K$12)*$K$2:$K$12)

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Sonja Mason, Alexandra Radu, Denise Lloyd
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Raj Mattoo
Mark Luke
terimeri dooriyan
Jack Aston
AndyC
Denise Lloyd
michael serna
mashal sana
Tiffany Kang
Leah Gillmore
Forum Stats:
Groups: 3
Forums: 24
Topics: 6219
Posts: 27276

 

Member Stats:
Guest Posters: 49
Members: 31903
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.