I'm trying to speed up a report i do on a monthly basis. trying to total sales by state but the number of rows my vary from month to month. states are always in order so for instance AL always follows AK.
is there a way to target a sum function based on my match results?
For ex. this month AK match returns 2 and Al returns 18 so i know i need the sum of Column J2:J17 but i cant seem to get the a formula for this.
You can use a SUMIF (or SUMIFS) function for this - the syntax is:
=SUMIF(criteria_range, criteria_value, sum_range)
Your sum_range seems to be J2:J17 from what you have said, but I don't know what range you are using for your states. The criteria_value can refer to a cell which contains a particular state.
It usually helps if you attach a sample Excel workbook to one of your posts - the yellow banner at the top of the screen gives details of how to do this.
Hope this helps.
Pete
since I worked on something I thought I'd post it even though Pete gave you the basics for the answer.
something like this...
=SUMIFS(B2:B13,J2:J13,">="&DATE(2019,10,1),J2:J13,"<"&DATE(2019,11,1),C2:C13,"AL")
where col B is the data you are summing, J has the dates and C has the states.
now, you can set up dates into a table and just reference the table for example and you can do the same for the state abbreviations so instead of changing AL for example you can point it at X2 as a possible location for the state table to begin.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks