Solved Using SUMIFS for filtered cells only

June 6, 2016 at 05:50:58
Specs: Windows 7
2 questions regarding SUMIFS:

1. I have a SUMIFS formula giving me results based on 2 criteria but when I apply a filter for any number of reasons (sales person, month of sale, etc...) I still get the total of all cells rather than just the visible ones, can someone help me out?
range = Pipeline!$F$6:$F$794
criteria 1 range and cell = Pipeline!$AB$6:$AB$794,A3 - this is my branch
criteria 2 range and cell = Pipeline!$I$6:$I$794,MONTH(K1) - this is a cell that has a month(J1) formula to give me the month I am looking for for the month of the sale date I want

Here is my current formula:
=SUMIFS(Pipeline!$F$6:$F$794,Pipeline!$AB$6:$AB$794,A3,Pipeline!$I$6:$I$794,MONTH(K1))
2. When I add the month(k1) formula at the end I get a zero; I want the sum of that month


See More: Using SUMIFS for filtered cells only

Report •


#1
June 6, 2016 at 07:09:42
The MONTH() functions requires a full date

Are you sure K1 is even getting a proper number?

Why use month(K1), which probably will not work,
when month(J1) gives you the same thing?

.


MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#2
June 6, 2016 at 14:21:59
K1 returns the month number but neither of them work in the formula, they both return 0

Report •

#3
June 6, 2016 at 18:18:49
✔ Best Answer
Take a look at this YouTube video, and see if it solves your problem.

https://www.youtube.com/watch?v=jMm...

It gives a good example of the use of the SUBTOTAL() function
which is probably a better function to use.

Also, in regards to MONTH(K1) and MONTH(J1)

MONTH(K1) - this is a cell that has a month(J1) formula to give me the month I am looking for for the month of the sale date I want

If MONTH(J1) references a real date in cell J1 then you should get the MONTH
for that date.
If MONTH(K1) references the cell that contains the result of MONTH(J1)
all your going to get is a single digit.

MIKE

http://www.skeptic.com/


Report •

Related Solutions


Ask Question