Problem with sumifs function

[Jonno1]

Hello,
I'm having problems with a sumifs function - could be something to do with the criteria for date syntax perhaps, summing total costs for each category of costs between 2 dates?

range to sum cost_data = Cost_data!$F$1.$F$1000

data range based on this cost data = Cost_data!$A$1.$A$1000

1st date criteria = Cost_data!$A$1.$A$1000,">=&$G$2" - date in cell $G$2

2nd date criteria = Cost_Data!$A$1.$A$1000,"<=&$G$3" - date in cell $G$3

3rd criteria = Cost_Data!$C$1.$C$1000,B14 - based on the value in cell B14 - category of costs.

Any help greatly appreciated!

[Jonmo1]

Try

=SUMIFS(Cost_data!$F$1:$F$1000,Cost_data!$A$1:$A$1000,">="&$G$2,Cost_Data!$A$1:$A$1000,"<="&$G$3,Cost_Data!$C$1:$C$1000,B14)

[etaf]

you have " around the cells
so
">=&$G$2"
should be
">="&$G$2
same on the other formulas

[Jonno1]

Hello,
Thanks for these suggestions, I've tried them in the new formula but with the second condition I get an error, excel highlights the following in the second condition in black/bold :

"<="&$G$3

when I try and input the criteria a second time with "<=&$G$3,

I get a suggested amendment to:

Cost_data!$A$1:$A$1000,">="&$G$2,Cost_Data!$A$1:$A$1000,""<=&$G$3

I'm not too sure whats going on here? To be on the safe side, if the function still exists in excel 2010, I would use DSUM with a criteria box hidden away.

[Jonmo1]

This works for me

=SUMIFS(Cost_data!$F$1:$F$1000,Cost_data!$A$1:$A$1000,">="&$G$2,Cost_Data!$A$1:$A$1000,"<="&$G$3,Cost_Data!$C$1:$C$1000,B14)

Can you post the actual formula you tried to enter?
Or even better, post a sample book?

[Jonno1]

Hi, file attached, worksheet is 'profit and loss account', relevant column where all the sumifs formulas are is column D, dates are G1 and G2, expense categories in column B.

[Izandol]

Formula must be:
=SUMIFS(Cost_data!$F$2:$F$1000,Cost_data!$A$2:$A$1000,">="&$G$2,Cost_data!$A$2:$A$1000,"<="&$G$3,Cost_data!$C$2:$C$1000,B14)
note two changes made.

[Jonno1]

Hi, thanks for the above, I had a quick look last night at the revised suggestions, dates seem to work ok, but with the final condition, should this be expressed in the formula as "B14"?

[Izandol]

No because it is reference to a cell not to actual text "B14".

[Jonno1]

Hi, some progress with the above, the conditions relating to the dates now work OK, but I have totals of 22 and 44 for indexing & marketing respectively, in sheet 'Profit and loss' for the period 1st January 2014 to 31st January 2014. However, looking at the data in sheet 'cost_data' suggests that these costs for the period under consideration should be more.

[Izandol]

Many of the dates in the cost data sheet are stored as text - you may see this because they align to left of column by default. You may convert to true dates by copying empty cell, then select date column and paste special, Values, Add.

[Jonmo1]

Your dates in column A on the Cost_Data sheet, are not all really dates.
Most of them are just text strings that look like dates.

Try copying a blank cell, then highlight that whole column
Right Click - Paste Special - Values - Add - OK

[Jonno1]

Thanks for this, I thought there would be something wrong with the date format as I did notice the difference in text alignments in the cell. The dates are entered via textboxes on a userform, I will now change the VBA code using Cdate function, and put the thread as 'solved' if this works.

[Izandol]

You must always use CDate if you pass date from userform textbox to a worksheet!