using sumifs with an extra

Question

I have the formula =SUMIFS(J32:J44,L32:L44,"Jan") on the sheet called "OrderShipmentDates" which works well, but I need to add an extra Criteria where cell BUdget!B4 (=DATE(YEAR(TODAY()), MONTH(TODAY()), 1) also needs to equal Jan. How do I include that extra criteria?

Answer 1

In SUMIF, all ranges should be equal. So, second parameter of B4 should also be B4:B16 assuming B4:B16 contain dates.

But SUMIFS will not support a formula inside it.

=SUMPRODUCT(J32:J44 * (L32:L44="Jan") *(TEXT(Budget!B4:B16,"mmm") ="Jan"))

But if you are looking that only B4 should be equal to Jan, then B4 can't be part of SUMIF.

Probably, you are looking for a formula of this kind

=SUMIFS(J32:J44,L32:L44,"Jan") * (TEXT(Budget!B4, "MMM) = "Jan")

Answer 2

Thank you

Much appreciated

David

Answer 3

Dear Vijay.

Thank you for your reply.

The answers I receive is #VALUE!

Is this because of the format of the months????

For example:-

THE FIELD L32:L44 IS LIKE 19/01/2025 which is formatted as =TEXT(K32,"MMM") .

and Budget!B4 =DATE(YEAR(TODAY()), MONTH(TODAY()), 1).

I hope you can help

Much appreciated

David

Answer 4

You can use following

=SUMIFS(J32:J44,L32:L44,"Jan", Budget!B4, "Jan")