SUM IF - Quarterly Calculations

[dixiebritt]

I am needing a formula in my revenue column is only added for the Quarter chosen in column A

[TABLE="width: 500"]
<tbody>[TR]
[TD]Quarter[/TD]
[TD]Revenue[/TD]
[/TR]
[TR]
[TD]Q1[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]Q2[/TD]
[TD]250[/TD]
[/TR]
[TR]
[TD]Q3[/TD]
[TD]350[/TD]
[/TR]
[TR]
[TD]Q4[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]Q1[/TD]
[TD]2500[/TD]
[/TR]
</tbody>[/TABLE]

I want a formula that will extract only the revenue for each time "Q1" is listed. This can not be a pivot table, as it is pulling the data into another worksheet.

 

[gaz_chops]

Try

=sumifs($B$2:$B$100,$A$2:$A$100,"Q1")

 

[dixiebritt]

yes , thank you - i had it backwards

 

[gaz_chops]

You're welcome.

 

[dixiebritt]

Question - what if my data is on-going and I want my formula to auto calculate based on new entries. for example i have 100 rows, but only 5 rows of data as listed above. then next week i add another 5 rows of data with the same criteria. i want my formula to auto calculate based on data i put in. I changed the row number from 5 to 100, but it is returning an "value" error code?????

 

[gaz_chops]

It should work! Did you change the 5 to 100 in both parts? They have to be the same size.

=sumifs($B$2:$B$100,$A$2:$A$100,"Q1")

 

[dixiebritt]

OMG - long day...operator error

 

[gaz_chops]

lol easy done