array formula to return sum of values based on date and category code

[usmc0331]

Hi all, bit new to array formulas and having some trouble working through this issue.

On the template tab in cell C5 I am trying to create an array formula that captures the category and corresponding dates from the "12 month sales" tab and sum them for each respective date.

Thanks for the help!

Excel Array Problem.xlsx

[FDibbins]

I tested for a few of the items on TEPLATE against 12 MONTH, and couldnt find any matches?

I dont think you need an array formula, just use sumifS()

[usmc0331]

I'm having trouble writing it with a sumif formula. I'm trying to populate the table by matching the category codes and dates on the table with the corresponding "sales dollars" column on the 12 month sales tab.

I'm using this array formula in C5

=SUM(('12 Month Sales'!salescategory=Template!$B5)*('12 Month Sales'!datefile=Template!C$3)*('12 Month Sales'!$J$3:$J$3476))

It is not returning the summed total for each month/category for the sales dollars column.

Any help would be appreciated

[FDibbins]

I think you missed that I suggested sumifS(), not sumif()?

[usmc0331]

I'm having trouble with the formula, I have:

=SUMIFS('12 Month Sales'!$H$3:$H$3476,'12 Month Sales'!salescategory,Template!$B5,'12 Month Sales'!datefile,Template!C$3)

(Salescategory being the named range of all the sales categories and datefile being the same for the dates)

can you offer any advice on the formula?

Thank you

[FDibbins]

What trouble are you having?

When I enter your formula in Template C5, I get 1568. Copied down and across, it seems to work fine?
1568...538...17.......
0..........10....0..........

[usmc0331]

when I enter the formula in I'm getting " - " as a result in each cell when I drag copy it down and across.

It will return the values for some of the cells, but will only return one value for each row.

for example, there are multiple categories that have sales on Apr-04, but the formula only returns a value for the "manholes carbon" category with a value of 5 in cell C18. The rest of the column is filled with "-" as a value.

[FDibbins]

I dont know what you are doing wrong, but once again, I copied your formula into C5, and copied it down and across - and got values in all rows and columns - except for where teh category doesnt exist.

See the attached