Need help with formula or function for numbers that change monthly

[johnanacater]

I am having the hardest time trying to figure out how to show my monthly numbers from a budget detail worksheet onto the budget summary worksheet. Here is a sample of what my summary worksheet looks like:
CurMnth YTD Budget
Air Surv 10 245 816
Enf Cas 24 36 1550
Air Perm 26 170 1500

Here is a sample of my monthly detail:

AirSurv EnfCas AirPerm
JUL 235 12 144
AUG 10 24 26
SEP
OCT
NOV
DEC
TOTALS 245 36 170

How do I get each months to show under Current Month and I have a file of the sample data?

 

[WinteE]

Using month numbers instead of JUL, AUG, SEP makes it possible to get both CurrMnth and YTD.

For these formulas the monthly detail is :

Range A2:A4 = 7, 8, 9
Range B1:D1 = AirServ, EnfCas, AirPerm
Range B2:D4 are the figures
Range A8 is your current month number


CurrMnth
=SUMPRODUCT(($A$2:$A$4=$A$8)*($B$1:$D$1=$A10)*($B$2:$D$4))

YTD
=SUMPRODUCT(($A$2:$A$4<=$A$8)*($B$1:$D$1=$A10)*($B$2:$D$4))

Success,

Erik

 

[johnanacater]

I tried it and it is not working for me. Maybe I am not entering the formula in the right cell. I put the formula in cell B2 and then I was going to copy it down but it is not working. In my actual spreadsheet I have 32 categories. Also, the actual monthly totals are on another worksheet in the same book.

 

[WinteE]

You are right, the formula is not entered in the right cell.

Cell B2 should contain the value 235, as in your monthly detail. C2 = 12, D2 = 144 etc.
Please enter the formulas in cells A10 and A11.

As the data are on another worksheet you'd best use named ranges.

 

[Peter_SSs]

If the headings were consistent this would be easier (eg "Air Surv" on one sheet and "AirSurv" on the other). Also, in your sample the headings across the top of the detail sheet are (apart from the space issue already highlighted) laid out identically to the headings down the left of the summary sheet. If this is always the case, the formula below could be simplified.

Anyway, this may give you a starting idea.

Formula in B2 of Summary is copied down.

Excel Workbook
	A	B	C	D
1		AirSurv	EnfCas	AirPerm
2	JUL	235	12	144
3	AUG	10	24	26
4	SEP
5	OCT
6	NOV
7	DEC
8	TOTALS	245	36	170
Detail

Excel Workbook
	A	B	C	D
1	24/08/2010	CurMnth	YTD	Budget
2	Air Surv	10
3	Enf Cas	24
4	Air Perm	26
5
Summary

 

[johnanacater]

Thank you Peter SSs for your assistance with my delimma, but I have not been able to get it to work. I have my spreadsheet setup just like yours and it is not working. Is there something that i am missing?

 

[Peter_SSs]

If it is not working then there must be something different between your setup and mine. A couple of things to investigate/report.

1. When you say it is not working what do you mean? Did you get an error? Nothing? Wrong answer?

2. In your 'Detail' sheet can you confirm that the month names in column A are text and not dates formatted to just show the month? Assuming the same layout as mine, in a blank cell put =ISNUMBER(A2)
What does that return?

3. If you have not done so already, start a new workbook and copy the data from my post into the respective sheets (you may need to delete the first column where the row numbers also get copied in). In 'Summary' delete the date in A1 and the three numbers in B2:B4 then copy each formula from my post and paste into the relevant cell. Copy B2 down to B4. Does it work in this new workbook?

You might also investigate posting a small screen shot of your sheets so I can copy yours and be more sure of the exact layout. I've used Excel jeanie but there are a number of ways including ...

Excel jeanie
or
RichardSchollar’s beta HTML Maker
or
Borders-Copy-Paste

 

[johnanacater]

Here is a copy of my worksheets

Excel Workbook
	A	B
6	25/08/2010	FY'11
7		Current
8	ACTIVITY INDICATORS	Month
9	Air Investigations	#N/A
10	Air Surveillance	0
11	Enforcement Cases	0
12	Air Permitting Review	23
13	Air Monitoring	0%
14	Handheld Sampling	0
15	Criteria Pollutants	0
16	Lake/Bayou Samples	291
17	Field Measurements	2,000
18	Complaints Investigations	76
19	Special Waste Investigations	4
FY11 Total

Excel Workbook
	A	B	C	D	E
3		Air Invest.	Air Surv.	Enf. Cases	Permit Reviews
4	Jul	10	0	0	23
5	Aug	15
6	Sep				0
7	Oct				0
8	Nov				0
9	Dec				0
10	Jan				0
11	Feb				0
12	Mar				0
13	Apr				0
14	May				0
15	Jun				0
16	TOTALS	25	0	0	23
Detail

 

[Peter_SSs]

OK, your problem (using my suggested formula) is your headings. As I mentioned previously your original data headings differed by a space. In these sample the headings are quite different ('Enforcement Cases' v 'Enf. Cases' etc).

However, it seems your headings are in the same order in each sheet, so try this (I have added a bit more data on the Detail sheet). You may need to adjust the ranges slightly to suit your data. It does depend on the corresponding headings being down column A of the Total sheet and across row 3 of the Detail sheet.

Excel Workbook
	A	B	C	D	E
3		Air Invest.	Air Surv.	Enf. Cases	Permit Reviews
4	Jul	10	0	0	23
5	Aug	15		8	26
6	Sep				0
Detail

Excel Workbook
	A	B
6	25/08/2010	FY'11
7		Current
8	ACTIVITY INDICATORS	Month
9	Air Investigations	15
10	Air Surveillance	0
11	Enforcement Cases	8
12	Air Permitting Review	26
13	Air Monitoring	0
FY11 Total

 

[johnanacater]

Thank you so much Peter SSs, you have helped me to save a lot of duplicated keystrokes.