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 wantHere 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

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

message edited by mmcconaghy

K1 returns the month number but neither of them work in the formula, they both return 0

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 wantIf 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

Ask Your Question

Weekly Poll

Do you think manufacturers should do more to reduce phone and tablet usage among kids?

Discuss in The Lounge

Poll History