LinkBack URL
About LinkBacks
In worksheet "Week of 0316 - Evaluator Perf." Cell C9, i want to show the sum of:
from worksheet "week of 0316 - Dashboard", all the cells in column G that show a 0 value but only for QA 1 (see column H).
In worksheet "Week of 0316 - Evaluator Perf." Cell C9, i want to show the sum of:
from worksheet "week of 0316 - Dashboard", all the cells in column G that show a 0 value but only for QA 1 (see column H).
Hi,
You need to get rid of the #DIV errors in the Dashboard sheet, say something like this
=IF(O2="","",AVERAGEA(O2,V2,AC2,AJ2)) copied downwards
Then in Elevator Perf sheet try this in C9
=SUMPRODUCT(--('Week of 0316 - Dashboard'!G2:G129=0),--('Week of 0316 - Dashboard'!H2:H129="QA 1"))
oldchippy
-------------
Blessed are those who can give without remembering and take without forgetting
If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.
Click here >>> Top Excel links for beginners to Experts
Forum Rules >>>Please don't forget to read these
Since you are working in 2007... try:
=SUMPRODUCT(--('Week of 0316 - Dashboard'!H:H=$C$4),--('Week of 0316 - Dashboard'!G:G=0))
You will get a #DIV/0 Error.. go to your other sheet and change the formula in G2 to: =IFERROR(AVERAGEA(O2,V2,AC2,AJ2),"") and copy down.... this will eliminate the errors...
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Hi all -
I fixed the error as you recommended but made a mistake in my request. I need to count, not sum. Silly me
Thanks a million
Bruno
That formula does count.
Outstanding... simply amazing
But because I do not really understand the formula, my other problem is to do the same for cells D4, E4, F4, H4, I4, J4 and k4
Hi,
More information on SUMPRODUCT can be found here
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
each argument, eg. --('Week of 0316 - Dashboard'!H:H=$C$4) checks a specific condition.. this one says to check if column H is equal to C4... putting multiple arguments in the formula separated by commas, checks that each of the conditions is met in the same rows before a TRUE is returned to give a count.Originally Posted by bmunoz
Try it on your other cells... If you still can't get it, repost.
I was looking at the wrong worksheet. I figured it out, thanks!
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
I just have one more question.
I would like to do the same thing for column O, V, AC and AJ from the dashboard worksheet, but adding them up together to get the total # of autofails into cell C11 in the Evaluator Perf. worksheet.
I have the formula
=COUNT(SUMPRODUCT(--('Week of 0316 - Dashboard'!H:H=$C$4),--('Week of 0316 - Dashboard'!O:O=0)), SUMPRODUCT(--('Week of 0316 - Dashboard'!H:H=$C$4),--('Week of 0316 - Dashboard'!V:V=0)), SUMPRODUCT(--('Week of 0316 - Dashboard'!H:H=$C$4), --('Week of 0316 - Dashboard'!AC:AC=0)), SUMPRODUCT(--('Week of 0316 - Dashboard'!H:H=$C$4),--('Week of 0316 - Dashboard'!AJ:AJ=0)))
what am I missing?
Not sure if this is exactly what you mean, but try:
Please Login or Register to view this content.
The + signs mean OR.
tried it and it returned the number 26. The total number of autofail should be 4. I'll continue to play with your formula.
Keeping fingers crossed
Not working so good. Any suggestions?
Are there blanks in any of the ranges in columns O, V, AC, AJ where H = QA 1?
Maybe try instead:
This formula can be copied across to D11, E11 and F11Please Login or Register to view this content.
Last edited by NBVC; 03-17-2009 at 02:02 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks