Counting Non-Consecutive columns where it matches a criteria

[mandye]

I have a spreadsheet which has columns for Planned and Actual figures by period in the following layout



1 1 2 2 3 3
Planned Actual Planned Actual Planned Actual
1 3 1 0 1 0

The figures going across the top of the table represent periods, the second row states whether it is planned or actual and the last row gives the figure.

I would like to be able to count the total number of instances that occur for the Planned and Actuals, where the period number from the top matches the periods so far that I would like to report on.

E.G., using the above information at Period 2 I have a total of 2 planned items and 3 actual items and since I only want to count the instances, this is now 2 planned items and 1 actual item.

Can anyone help me with a formula to get this. My actual spreadsheet is a lot more complicated than the above otherwise I would simply have hidden columns which give the figures side by side and use an offset formula. I can't use nested IF statements either since I can only use a maximum of 7 and I have 13 columns for each Planned and Actual.

Does anyone have simple solution for this which doesn't involve adding additional columns??

Yours hopefully


M

 

[Mark W.]

Re: Counting Non-Consecutive columns where it matches a crit

How "married" are you to this data layout?

 

[mandye]

Re: Counting Non-Consecutive columns where it matches a crit

Attached for life I'm afraid!, otherwise I would have taken the easier option and all the Planned Actual items together.

 

[IML]

Re: Counting Non-Consecutive columns where it matches a crit

As Mark suggests, the layout should be changed if at all possible. This would allow for subtotals, pivot tables, and other good consolidated tools to be used. These formula should work, but could provide potential performance problems down the road if used in masse.

Proposed thru period 2
=SUMPRODUCT(($A$1:$F$1<=2)*($A$2:$F$2="Planned"),$A$3:$F$3)
=SUMPRODUCT(($A$1:$F$1<=2)*($A$2:$F$2="actual"),$A$3:$F$3)

Replace these with cell references if you like.

 

[Mark W.]

Re: Counting Non-Consecutive columns where it matches a crit

Are these the results that you're seeking...

Book1
	A	B	C	D	E	F	G	H
1	1	1	2	2	3	3
2	Planned	Actual	Planned	Actual	Planned	Actual
3	1	3	1	0	1	0
4	1	1	2	1	3	1
5
Sheet2

 

[mandye]

Re: Counting Non-Consecutive columns where it matches a crit

Thanks to the both of you for replying so quickly but I have a couple of problems with your suggestions.

The SUMPRODUCT function is giving me a sum of the figures, rather than counting the instances, while the second function seems to be dependant on adding another row. Unfortunately, I have numerous rows of data under the column headings which need to use the same formula to calculate the instances, and it wouldn't be practical for me to insert this for each one.

Sorry for not mentioning this in my previous e-mail


Any other suggestions??

 

[mandye]

Re: Counting Non-Consecutive columns where it matches a crit

Actually - I've been able to use the second one by adding another IF statement so it reads:

{=COUNT(IF(A1:Y1<=2,IF(MOD(COLUMN($A$3:Y3),2)<>IF(Y2="Planned",0,1),1)))}

Thanks again Mr Excel!!

 

[mandye]

Re: Counting Non-Consecutive columns where it matches a crit



Sorry spoke too soon - I'm afraid that didn't work either!!

Still need help!

 

[IML]

Re: Counting Non-Consecutive columns where it matches a crit

Oops sorry. How about
=SUMPRODUCT(($A$1:$F$1<= 2 )*($A$2:$F$2= "Planned" )*($A$3:$F$3<>0))

and
=SUMPRODUCT(($A$1:$F$1<= 2 )*($A$2:$F$2= "Actual" )*($A$3:$F$3<>0))

 

[Aladin Akyurek]

Re: Counting Non-Consecutive columns where it matches a crit

Book2
	A	B	C	D	E	F	G
1	1	1	2	2	3	3
2	Planned	Actual	Planned	Actual	Planned	Actual
3	1	3	1	0	1	0
4	2	4			4	7
5
6
7	2	2
8	Planned	Actual
9	3	2
10
Sheet1

The formula in A9, which is copied across, is...

=SUMPRODUCT((($A$1:$F$1<=A7)*($A$2:$F$2=A8))*(ABS($A$3:$F$4)>0))