Getting VALUE error in Excel using a sum range and criteria 1 with a single cell criteria 2

Question

I have a scenario where I have a table with some amount and some information relating to those specific amount.

I don't have a single cell with a date in it. What I am trying to do is a sumifs on the range which includes the amount and the specific data but the sumifs also contains a criteria referencing only a single cell which has the date but I am getting the value error for the formula. If I split into two parts and create a Cell containing the the sum on the criteria and then use this single cell in the sumif instead of the range it works

But how can I get this to work as one formula?

My formula looks something like this:

=SUMIFS(B2:B5,C2:C5,"YES",A1,A2) 

where the B range are the amounts, the C range are the criteria to match against and the two A cells are both dates I want to match up.

Answer 1

The ranges used in SUMIFS have to be symmetrical. They can be different columns but must contain the same number of cells. I don't think that SUMIFS is going to be able to handle the mix of ranges and individual cells that you are looking to do. One workaround would be to put the criterion from A2 into D1. The formula would look something like this: =SUMIFS(B2:B5,C2:C5,"YES",A2:A5,"CriterionfromA1",D2:D5,"CriterionfromD1")