SUMIF Formula with data range (newbie)

[9Redwood]

HI All,

Just wondering if someone could help with a SUMIF formula.

Currently, this formula =SUMIF('Historical Data'!$V$2:$V$4929,'Bin Activity'!$B2,'Historical Data'!$W$2:$W$4929) is looking at a tab full of raw historical data.

With regards to 'Historical Data'!$V$2:$V$4929, this range is subject to dates (Column V is sorted by date). This information is then populated into the summary page for analysis. I was just curious if I could implement a formula that looks at data conditionally by date range.

With regards to 'Bin Activity'!$B2, this is the summary page where column A is for July, B is for August, C September etc... Is there a way I can automate this formula so I dont have to recreate it with specific date ranges each month? Apologies if this question is not that challenging..

 

[AlphaFrog]

There are different methods you could use.

You could create a helper column on 'Historical Data' that extracts the month for each date in column V. Say that helper column is in column "Z" for this example. Put this in Z2 and drag it down for each date in column V
=TEXT(V2,"MMMM")

Then use a formula like this on the summary sheet.
=SUMIF('Historical Data'!$Z:$Z,'Bin Activity'!$B2,'Historical Data'!$W:$W)

Where B2 has the month as text e.g. January, February, March...


If you don't\can't use a helper column, you could use a SUMIFS formula if you have Excel 2007 or later, or use a SUMPRODUCT formula. With those functions, you could calculate the dates for Start-of-month and end-of-month for the month listed in B2 and return a total for a given month.

 

[9Redwood]

Thank you very much for the reply. Much appreciated.

 

[9Redwood]

HI Alphafrog,

Would you have the patience to help again? I have been searching the net for over an hour regarding sumifs and sumproduct. I just cant seem to get it.

If so, would you require any more info?

 

[Aladin Akyurek]

On Bin Activity (the summary page)...

=SUMIFS(
   'Historical Data'!$W$2:$W$4929,
   'Historical Data'!$V$2:$V$4929,">="&B2,
   'Historical Data'!$V$2:$V$4929,"<="&C2)

where B2 and C2 houses date criteria with B2 <= C2. The range B2:C2 installs effectively a date range.

Is this what you are looking for?

 

[9Redwood]

Yes, that it! So simple when entered like that. I feel silly!

Thank you Aladin and AlphaFrog for your excellent help!
Appreciate it

 

[Aladin Akyurek]

Yes, that it! So simple when entered like that. I feel silly!

Thank you Aladin and AlphaFrog for your excellent help!
Appreciate it

You are welcome. Thanks for providing us feedback.