sumproduct? sumif?

[Wauker]

Hello all, I'm using Excel 2003 and need help as to whether I need to use either of the subject formulas.
I have a number of sheets titled the Vehicle Registration Number, eg ABC-123, DEF-456, GHI-789 ETC. These have various columns where Colmumn A has a title of "Category" and the following are listed therefunder: Fuel, Repais, Hire, etc. Another Column has the "Total Costs" associated with these.
I need a Summary Sheet that lists the Vehicles (ABC-123 etc) across the columns in Row 1 and then in Rows 2, 3, 4 etc tally the costs of those in Column A, Fuel, Repairs etc.

Hope I have explained it well enough, cheers, thank you
Pete

[darkyam]

I think I get your layout, but it'd be best if you could upload a workbook with dummy data, preferably with a couple examples of correct outputs. Thanks.

[ysching]

I have a similar question but I encountered a little problem regarding these 2 functions too.

Date Category Amount
29/11/11 Entertainment 16
30/11/11 Dining 23
1/12/11 Dining 17
18/11/11 Dining 50
20/11/11 Dining 60
19/11/11 Petrol 26
20/12/11 petrol 30
21/11/11 petrol 115

Suppose the above data is in my excel, so A1= Date, A9=21/11/11, B1=Category,B9=Petrol and C1=Amount, C9=115.

so i want to make a function based on the criteria to add all my petrol spending for november 2011, and exclude anything that falls outside the range.

("K4"=01/11/2011)

I've come up with this formula of =SUMIFS(C2:C9,B2:B9,"Petrol",A2:A9,">="&K4,A2:A9,"<"&DATE(YEAR(K4),MONTH(K4)+1,DAY(K4)))

the answer for this function should be 141.

Managed to get this to work, but when i want to broaden the range; from C:C rather than C2:C9, B:B rather than using the formula B2:B9 and ditto for A:A, so that I can keep updating this data without having to adjust the function every single time, is there anything that I can add to the function to make that work?

managed to get the same result using sumproduct, the function is as follows, =SUMPRODUCT((Category=J7)*1, (Amount), ((Date)>=K4)*1, ((Date)<DATE(YEAR(K4), MONTH(K4)+1, DAY(K4)))*1).

J7=Petrol, Category=B:B, Amount=C:C and Date=A:A,

Although the result of both the function gives the same answer, there is one distinctive difference, the sumproduct function allows me to select the whole column whereas the sumifs is only letting me do the calculation on a specific range. Is there anything that I can do with the sumifs function that can produce the same result? That is to be able to select the whole column?

Also, as I am not the original producer of the sumproduct formula, can someone please enlighten me as to why I need to input the *1 and how can one built such formula as I am getting confused.

Thanks