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

mandye, it would be helpful if you supplied the desired results using the example that you provided in your original posting...

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

 

[mandye]

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

Thanks to everyone who helped me on this - in the end I opted to use the SUMPRODUCT function and my spreadsheet works great now -
hurrah! :P