Hello,
I was hoping someone could help with this challenge (at least for me!):
I am trying to run YTD counts for "all months prior to this one". For example, in February I only want to count January; in March I only want to count Jan and Feb and so forth.
Each month is in its own sheet/tab. The YTD sheet/tab is also on its own and where the formula should go.
I've tried using SUM, SUMIF, SUMIFS, SUMPRODUCT, INDIRECT and a couple of others with no such luck.
A simple SUMIF formula would work if all the data is in the same tab, one for ea employee and with columns representing the months .
=SUMIF(A1:L1,"<="&N1,A2:L2)
For example, the formula above works where:
A1:L1 is a range of months from Jan to Dec.
N1: is a cell containing this month.
A2:L2 is a range of values to sum.
When I tried to reproduced the above across the multiple tabs, I tried this:
=SUMIF('January 2013:December 2013'!B1,"<"&A1, 'January 2013:December 2013'!C2) where:
'January 2013:December 2013'!B1: is the "range" across the sheets where B1 holds the month name.
"<"&A1: A1 holds this month name in the YTD sheet.
'January 2013:December 2013'!C2: is the "range" across the sheets where C1 hold the values to add if the condition is met.
At any rate. Anything you can provide will be really helpful!
Thank you!
m@rcelo
<tbody>
</tbody><colgroup><col><col><col><col></colgroup>
<tbody>
</tbody><colgroup><col><col><col><col></colgroup>
<tbody>
</tbody><colgroup><col><col><col><col></colgroup>
I was hoping someone could help with this challenge (at least for me!):
I am trying to run YTD counts for "all months prior to this one". For example, in February I only want to count January; in March I only want to count Jan and Feb and so forth.
Each month is in its own sheet/tab. The YTD sheet/tab is also on its own and where the formula should go.
I've tried using SUM, SUMIF, SUMIFS, SUMPRODUCT, INDIRECT and a couple of others with no such luck.
A simple SUMIF formula would work if all the data is in the same tab, one for ea employee and with columns representing the months .
=SUMIF(A1:L1,"<="&N1,A2:L2)
For example, the formula above works where:
A1:L1 is a range of months from Jan to Dec.
N1: is a cell containing this month.
A2:L2 is a range of values to sum.
When I tried to reproduced the above across the multiple tabs, I tried this:
=SUMIF('January 2013:December 2013'!B1,"<"&A1, 'January 2013:December 2013'!C2) where:
'January 2013:December 2013'!B1: is the "range" across the sheets where B1 holds the month name.
"<"&A1: A1 holds this month name in the YTD sheet.
'January 2013:December 2013'!C2: is the "range" across the sheets where C1 hold the values to add if the condition is met.
At any rate. Anything you can provide will be really helpful!
Thank you!
m@rcelo
Jan-13 | Jane | Paul | Chris |
Projected Amount | 10,000 | 5000 | 43000 |
Actual Amount | 13000 | 1,200 | 75,000 |
Difference | 3,000 | -3,800 | 32,000 |
<tbody>
</tbody><colgroup><col><col><col><col></colgroup>
Feb-13 | Jane | Paul | Chris |
Projected Amount | 20,000 | 6,000 | 45,000 |
Actual Amount | |||
Difference |
<tbody>
</tbody><colgroup><col><col><col><col></colgroup>
YTD-13 | Jane | Paul | Chris |
Projected Amount | 10,000 | 5,000 | 45,000 |
Actual Amount | 13,000 | 1,200 | 75,000 |
Difference | 3,000 | -3,800 | 32,000 |
<tbody>
</tbody><colgroup><col><col><col><col></colgroup>