Sumif formula with validation list reference

[da_vide78]

Hi all.

I cant get my head around how to best craft this formula. I have a doc with 3 tabs for resources on a project.

tab 1 - Summary tab (resource names & this is where the formula and calculations sit) with a validation list with months in
tab 2 - resource Forecasts
tab 3 - resource Actuals

on the summary sheet i want to use a formula that pulls the data from each of the forecast and actual tables (Sumif) BUT only for the month selected in the validation list at the top.

I cant quite get my head to work it out without using loads of IF formulas.

Can someone suggest a simpler way?


Cheers

 

[GK039]

Hi,

Try the formula provided and change the ranges accordingly

For my example:
Cell B1 in Sheet1 (tab1 for you) is the validation list with months

Sheet2!A1:K12 is the forecast tab, where months are housed in cells A1:A12 and data in cells B1:K12

Sheet3!A1:K12 is the third tab (actuals), where months are housed in cells A1:A12 and data in cells B1:K12

Formula in Sheet1!B2
=SUM(INDEX(Sheet2!$B$1:$K$12,MATCH($B$1,Sheet2!$A$1:$A$12,0),))+SUM(INDEX(Sheet3!$B$1:$K$12,MATCH($B$1,Sheet3!$A$1:$A$12,0),))

George

Hi all.

I cant get my head around how to best craft this formula. I have a doc with 3 tabs for resources on a project.

tab 1 - Summary tab (resource names & this is where the formula and calculations sit) with a validation list with months in
tab 2 - resource Forecasts
tab 3 - resource Actuals

on the summary sheet i want to use a formula that pulls the data from each of the forecast and actual tables (Sumif) BUT only for the month selected in the validation list at the top.

I cant quite get my head to work it out without using loads of IF formulas.

Can someone suggest a simpler way?


Cheers

 

[snoopyhr]

Assumed data on Forecast and Actual sheets are summerized by month, then another approach will be :

Excel Workbook
	A	B	C
1	Summary
2	Date	Mar
3	Resources	Forecast	Actual
4	Resource 1	3989	2995
5	Resource 2	4661	3806
6	Resource 3	3047	1622
7	Resource 4	2652	3204
8	Resource 5	3929	1890
9	Resource 6	3187	2212
Summary

#VALUE!

 

[T. Valko]

Hi all.

I cant get my head around how to best craft this formula. I have a doc with 3 tabs for resources on a project.

tab 1 - Summary tab (resource names & this is where the formula and calculations sit) with a validation list with months in
tab 2 - resource Forecasts
tab 3 - resource Actuals

on the summary sheet i want to use a formula that pulls the data from each of the forecast and actual tables (Sumif) BUT only for the month selected in the validation list at the top.

I cant quite get my head to work it out without using loads of IF formulas.

Can someone suggest a simpler way?


Cheers

Do you have a column of dates or a column of month names?

Where do we find the months and the range to sum?

Need more specific info.

 

[da_vide78]

Thanks to folks for your replies so far.


I've managed to sort out the forecast bit, it's now the actuals data i need to pull in.

So at the moment i have a data dump on the 'Actuals Data' tab. The data is a huge list of transactions with 3 key columns.

Column B = Period (1=Apr, 2=May, 3=June, etc (Goes on financial year))
Column J = Resource name (Resource names in my summary table have been made to match these)
Column N = Amount

I need a formula to sum the Amount (N) for a given period (B(Eg. Period 1)), and by Name (J).

I used an IF and SumIF but to no avail..

=IF('Actuals data'!Period="1",SUMIF('Actuals data'!A5:O2000,Sheet1!C8,'Actuals data'!N2:N2000),FALSE)


Can anyone show me where i may be going wrong?