Formula to add up a subtotal of a filter range between two date

[EddyTheEagle]

Hi

I need to sum up the sales in the range R56:R5044
I have a daily date range N56:N5044

and 2 date criteria in Q5 & Q6 which are using the following dates:
01-02-2019
01-03-2019.

I have a Sumif which is working (=SUMIFS(R$56:R$5044,$N$56:$N$5044,">="&$Q5,$N$56:$N$5044,"<"&$Q6))

However I want to be able to filter on various criteria between A56:A5044 and after filtering see the sum of the sales (like a subtotal).

Can anyone who is good with formulas help (If you need more clarification please let me know)


Thanks in advance
Eddy

 

[Logit]

.
https://exceljet.net/formula/sum-visible-rows-in-a-filtered-list

 

[Marcelo Branco]

Since your formula has several conditions, I think you should take a look at
https://chandoo.org/wp/formula-forensics-023/

M.

 

[Eric W]

Try:

=SUMPRODUCT(SUBTOTAL(103,OFFSET(R$56,ROW(R$56:R$5044)-ROW(R$56),0))*($N$56:$N$5044>=$Q5)*($N$56:$N$5044<=$Q6)*R$56:R$5044)

 

[EddyTheEagle]

Thanks is working!!! Takes time but I guess is the amount of data and columns.

Have a nice day!

 

[Eric W]

Glad we could help.