[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

I have some errors in a formula which are not calculating correctly

Posted on 2011-10-12
7
Medium Priority
?
221 Views
Last Modified: 2012-05-12
Hi,

In the attached spread sheet, worksheet 'Analyse' the sums are not calculating correctly.  I need the sum to look at worksheet 'Master Sheet' column 'H' as then the sum will tell me then the quantities for each month of the types of problems we have.
grn-spreadsheet.xls
0
Comment
Question by:Simonrepro
  • 3
  • 3
7 Comments
 
LVL 50

Expert Comment

by:barry houdini
ID: 36956144
Try this version in B2 copied across and down to incorporate the values from column H

=SUMPRODUCT(--('Master Sheet'!$J$2:$J$385=$A2),--(TEXT('Master Sheet'!$F$2:$F$385,"mmm-yy")=TEXT(B$1,"mmm-yy")),'Master Sheet'!$H$2:$H$385)

regards, barry
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 36956153
See attached with formula applied

regards, barry

grn-spreadsheet-barry.xls
0
 

Author Comment

by:Simonrepro
ID: 36956172
Hi Barry, This cannot be correct as in October ie this month for 25 - Faulty Chip there does not appear to be any but there are loads on the master spreadsheet for this one?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Assisted Solution

by:branaf
branaf earned 600 total points
ID: 36956262
Simon,

The main issue was a typo. You may want to ensure that in the internal error column only can be entered values from the list.


grn-spreadsheet.xlsx
0
 

Author Comment

by:Simonrepro
ID: 36956322
Hi Barry, it is still incorrect as the quantity says 14 but I can see 37 just in the bottom 8 cells of the quantity column.
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 1400 total points
ID: 36956388
You need two changes from your original - the formula change that I proposed (otherwise you simply get a count of each fault rather than the sum of column H for each count) AND the fix that branaf made for you so that the right fault values are in column J of master sheet - see revised with both

barry
grn-spreadsheet-barry.xls
0
 

Author Closing Comment

by:Simonrepro
ID: 36956410
I hope I have done the right thing here with the point but thank you all for helping anyway!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question