Summing a range of cells when date is found in a range of other cells

[bozzy71]

Hi,

I have the following formula to search for a month and year in a cell, and then add the value from another cell =IF(TEXT(K6,"myy")="521",E6,"") - however, I'd like to search a range of cells (eg K6:k50 for the month of May) and return any figure as previous from the range of E6:E50 if May is found. Therefore if in K6 and K12 and K20 "may" is found, then the figure in E6, E12 and E20 are returned and added together and so on. Hope that makes sense!

thx

 

[Fluff]

How about

=SUMIFS(E6:E50,K6:K50,">=01/05/2021",K6:K50,"<=31/05/2021")

 

[jasonb75]

Defining month and year as 521 is not really a formula friendly method. You could get around it with sumproduct as below but I would advise rethinking your approach to make use of @Fluff's method, or something similar.

=SUMPRODUCT(--(TEXT(K6:K50,"myy")="521"),E6:E50)

My personal (formula) preference is to use a combination of SUMIFS and EOMONTH with a proper date in cell on which to base the criteria.