Summing non-contiguous cells with conditions?

[LolSA]

Hi, I'm new to the forum, but I have read through many of the threads here while trying to figure out how to write a formula to get the results I need. I hope you will be able to help me get there!

I am running Excel 2003 at work. I sometimes work on this project at home where I have Excel 2010 and Windows 7.

I have the following report spreadsheet that users enter data into:

Excel Workbook
	A	B	C	D	E	F
5			Lab 1	Lab 2	Lab 3	Lab 4
6	2011	Jan	N	Y	N	N
7		Feb	N	N	N	N
8		Mar	N	N	N	N
9		Apr	N	N	N	N
10		May	Y	N	N	N
11		Jun	N	N	N	N
12		Jul	N	N	N	Y
13		Aug	N	N	N	N
14		Sep	N	N	N	N
15		Oct	N	N	N	N
16		Nov	N	N	Y	N
17		Dec	N	N	N	N
EVP Inspection

Excel Workbook
	A	B	C	D	E	F	G
4			Lab 1	Lab 2	Lab 3	Lab 4	Corp Totals
5	Jan	Rating	-1000	500	-	-1000	1
6	Feb	Rating	-1000	-1000	-	-1000	0
7	Mar	Rating	-1000	-1000	-	-1000	0
8	Apr	Rating	-1000	-1000	-1000	-1000	0
9	May	Rating	500	-1000	-1000	-1000	1
10	Jun	Rating	-1000	-1000	-1000	-1000	0
11	Jul	Rating	-1000	-1000	-1000	500	1
12	Aug	Rating	-1000	-1000	-1000	-1000	0
13	Sept	Rating	-1000	-1000	-1000	-1000	0
14	Oct	Rating	-1000	-1000	-1000	-1000	0
15	Nov	Rating	-1000	-1000	-1000	-1000	0
16	Dec	Rating	-1000	-1000	-1000	-1000	0
17
18		SEI by Facility	500	500	-1000	500	3
Excel 2010 Basically, the lab is required to have annual inspections with the user entering "Y" or "N" depending on whether this was completed in a particular month or not. Then I have a second spreadsheet which calculates the score (+500 points for completing the inspection during that month and -1000 for not doing so) EVP Inspection (2)

Excel 2010

Cell Formulas
Range	Formula
C5	=IF('EVP Inspection'!C6=""," - ",IF('EVP Inspection'!C6="Y",500,-1000))
C18	=MAX(C5:C17)

Now, I only want the lab to receive -1000 only if the lab did not perform an inspection at all in the year, but also see which month the inspection was performed. I have gotten around this in the last line by using MAX.

However, there is a 3rd spreadsheet that draws on this one.

Excel Workbook
	A	B	C	D	E	F	G
6			Lab 1	Lab 2	Lab 3	Lab 4	Corp Totals
138
139	Dec	SM	-	-	-	-	0.00
140		SJI	-	-	-	-	0.00
141		SGI	-	-	-	-	0.00
142		SVPI					0.00
143		SD	-	-	-	-	0.00
144		SHC	-	-	-	-	0.00
145		SII	-	-	-	-	0.00
146		ST	0.00	0.00	0.00	0.00	0.00
147		SLT	0.00	0.00	0.00	0.00	0.00
148
149		Overall SI by Facility	0.00	0.00	0.00	0.00	0.00
150
151		Overall Annual SI	0.00	0.00	0.00	0.00	0.00
OVERALL

Excel 2010

Cell Formulas
Range	Formula
C149	=SUM(C139:C148)
C151	=SUM(C17,C29,C41,C53,C65,C77,C89,C101,C113,C125,C137,C149)

It takes the data from the 2nd spreadsheet and several others and adds them to produce an overall monthly score.

However, and yes, I have finally reached the point I then need it provide me with an annual total for each lab. But I need it not to add all the totals as I had before I added this sheet, because as you remember, each lab gets 500 for completing, whereas if I add it as is, it will return a score that is out by -10 000! I have tried various formulae, all with little or no success. I need it to add all the totals for everything else normally, but if there was any month where the inspection was completed, I need it to only add 500, and if no inspection was performed, add -1000 (not 12*-1000).

In fact, now that I think about it, ideally, I would like the monthly scores to give me -1000 until such a time as a 500 was scored, and then every month after that should also receive 500, as the inspection was carried out, so they do not get penalised thereafter.

I have exhausted the Help function in Excel and I am at my wit's end! I think that I cannot see the woods for the trees anymore with respect to this and I am hoping that there is a really simple solution that I have missed.

I have tried to be as thorough and clear as I can be, but if I need to clarify anything, please let me know.

Thanks!

 

[Jerry Sullivan]

Welcome to the Board!

I'm guessing the lack of response to your question is that it is a bit hard to follow. The formula will probably be pretty simple if you can distill the question down to:

Your starting point (for example.. your first screen shot with the Y/N).
Your desired final result (mockup OVERALL without the actual formulas).

Also please clarify:
Do you need to have the intermediate "EVP Inspection (2)" worksheet for other purposes, or could this be eliminated if there is a simple way to get from EVP Inspection to OVERALL?

 

[LolSA]

Hi Jerry!

Yes, in hindsight, it was rather confusing. Thank you for the tips!

I would prefer to keep the intermediate spreadsheet, but I think that there is a more logical way of doing this.

Ok, let me try it this way.

I have the spreadsheet that users enter the information to:

Excel Workbook
	A	B	C	D	E	F
5			Lab 1	Lab 2	Lab 3	Lab 4
6	2011	Jan	N	Y		N
7		Feb	N	N		N
8		Mar	N	N		N
9		Apr	N	N	N	N
10		May	Y	N	N	N
11		Jun	N	N	N	N
12		Jul	N	N	N	N
13		Aug	N	N	N	N
14		Sep	N	N	N	N
15		Oct	N	N	N	N
16		Nov	N	N	N	Y
17		Dec	N	N	N	N
EVP Inspection

#VALUE!

Excel 2010




Then for the "Overall" monthly totals, I would simply link to the relevant cells in EVP Inspection (2).

I hope that is clearer?

Thanks again!

 

[Marcelo Branco]

Try this in EVP Inspection (2)

C5
=IF('EVP Inspection'!C6="Y",IF(COUNTIF(C$4:C4,500)=0,500),IF(AND('EVP Inspection'!C6="N",COUNTIF(C$4:C4,500)=0,COUNTIF(C$4:C4,-1000)=0),-1000,"-"))

copy across till F5 and down till row 16

HTH

M.

 

[Marcelo Branco]

Re-reading your OP i have a question:

Is possible two inspections in the same year? If so should the formula assign 500 for each one?

In this case use this in C5

=IF('EVP Inspection'!C6="Y",500,IF(AND('EVP Inspection'!C6="N",COUNTIF(C$4:C4,500)=0,COUNTIF(C$4:C4,-1000)=0),-1000,"-"))

M.

 

[LolSA]

Hi Marcelo

Yes, it is possible, if unlikely, so...

That's it!

Thank you so so so much!!!!!

I had tried a vaguely similar formula in the beginning, but couldnt manage to figure out the COUNTIF conditions properly. And then I went off on all sorts of tangents that kept getting me further and further away from any semblance of an answer!

Once again, thank you thank you, I appreciate the help!

 

[Marcelo Branco]

LolSA

You are welcome and tks for the feedback

M.