[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
Medium Priority
221 Views
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.
0
Question by:Simonrepro
• 3
• 3

LVL 50

Expert Comment

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

ID: 36956153
See attached with formula applied

regards, barry

0

Author Comment

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

LVL 2

Assisted Solution

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.

0

Author Comment

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

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
0

Author Closing Comment

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

Featured Post

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.
Suggested Courses
Course of the Month19 days, 7 hours left to enroll