if using OR for 12 cells calcualtion?

[exceliz]

Hi

wondering if this is posible.

I have 12 months of data in cells F7 H7 J7 etc every other cell to cell AB7 showing the Target
i also have 12 months of data in cells G7 I7 K7 etc showing the actual which at the end i have just summed up a cummulative total to the month i am reporting on.

but i want to sum the target up according to what month it is? if i do a sum it just sums up the years with no current month comparison available.

thanks Liz

 

[GlennUK]

How about this:

Excel Workbook
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
5		Tgt	Act	Tgt	Act	Tgt	Act	Tgt	Act	Tgt	Act	Tgt	Act	Tgt	Act	Tgt	Act	Tgt	Act	Tgt	Act	Tgt	Act	Tgt	Act
6	month	1	1	2	2	3	3	4	4	5	5	6	6	7	7	8	8	9	9	10	10	11	11	12	12
7		34	32	36	32	38	32	36	32	37	32	44		42		39		36		38		37		41
8	Act	160
9	Tgt	181	- only sums values that have a matching non-zero value
Sheet

 

[exceliz]

i get an N/A

but my actuals have 0 in not blanks?
they are linked to the data source which shows on a graph and is not plotted as 0's?

coudl this be why?

Thanks for your help

 

[GlennUK]

I used range F7:AC7 which is actually one too big, and so tested AD7 when it's not needed. Does this work:

=SUMPRODUCT(--(MOD(COLUMN(F:AB),2)=0)*(F7:AB7)*--(G7:AC7>0))

?

 

[exceliz]

EXCELLENT

thanks v much. dont quite understand why the extra column made it fail but GREAT
Thanks

Liz x

 

[GlennUK]

Glad to help

 

[snoopyhr]

Another alternative I used Sheet10 to test , change the sheet reference as required.

Excel Workbook
	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1	Jan	Jan	Feb	Feb	Mar	Mar	Apr	Apr	May	May	Jun	Jun	Jul	Jul	Aug	Aug	Sep	Sep	Oct	Oct	Nov	Nov	Dec	Dec
2	Target	Actual	Target	Actual	Target	Actual	Target	Actual	Target	Actual	Target	Actual	Target	Actual	Target	Actual	Target	Actual	Target	Actual	Target	Actual	Target	Actual
3	1000	890	2000	1000	4000	5600	3000	4000	4000	3500	2000	2000	1500	2000	1000		2000		3000		2000		1500
4
5	Beg	Jan
6	End	Aug
7
8	Total:	From Jan toAug
9	Target	18500
10	Actual	18990
11
Sheet10

#VALUE!

</td></tr></table></td></tr></table>