Sum data from tab A to tab B with sumifs formula

I am trying to sum data from tab A to tab B
in tab A I have column A with item, and than for each day of the month 3 columns, one is List( different criteria for list to chose), one is Q and one is $ ,
on tab B I am trying to add q (quantity) for that item for specific time period,
however, my formula returns as Value, and I believe it is because of the list column,
any ideas what else I can do
thanks

Im assuming you mean that your getting the #VALUE! error, which happens when your formula is trying to do a calculation/s with something that isn't a number (e.g. text). Can you post an example illustrating the problem, and including your formula?

tab b
sumprod(--(a!$C$4:$N$4>=b!$C$5)*--(a!$C$4:$N$4=b!$D$5)*--(b!$A7=a!$A$6:$A$8)*--(a!$C$5:$N$5=b!$C$6)*--(a!$C$6:$N$8))

I tried to upload file, have some issues so this is what I am looking at

yes, so if I have a validation list, as part of the my range, and iti is not a number, how can I fix that

I*m sorry, but based on the formulae on Sheet b, I cannot understand what totals you are trying to calculate. Can you post the values that you are expecting in C7, C8 and C9 showing how you arrive at them?

Formula should be

sumprodUCT(--(a!$C$4:$N$4>=b!$C$5)*--(a!$C$4:$N$4=b!$D$5)*--(b!$A7=a!$A$6:$A$8)*--(a!$C$5:$N$5=b!$C$6)*--(a!$C$6:$N$8))

You are checking a date is >= (c5) AND = (d5) : it cannot be both do mean <= D5 ?

I would strongly recommend you change the layout of the data in Sheet a as is not in form which enables formulas to easily replicated.

Originally Posted by JohnTopley

Formula should be

sumprodUCT

Yes, I picked up the spelling error, but because the arrays don't have the same number of elements, and the last one contains non-numeric data, this causes #VALUE! results. Also I think that the dollar figures might be incorrect in Sheet b because the qty is multiplied by the previous dollars instead of the price?

Data formatted as Sheet3 would be better for doing calculations.

Originally Posted by Ninocka

I am trying to sum data from tab A to tab B
any ideas what else I can do

Ive taken a guess that your trying to total the quantities between the dates specified in b!C5:D5. Below is my formula that goes in b!C7 copied down.
=SUMPRODUCT(--(a!$C$4:$N$4>=b!$C$5)*--(a!$C$4:$N$4<=b!$D$5)*--(a!$C$5:$N$5=b!$C$6)*IFERROR(INDEX(--(a!$D6:$N6<"a")*a!$D6:$N6,1,),0))

I have made a small change to your data in the attachment, which is to move the date in Sheet A into the same column as the "Q" (quantity).
Also note that there might be an anomaly with your dollar values in Sheet A as starting with the second day, your multiplying Qty * previous dollar value.
Hope that helps, but if its not what your wanting then get back to us.