SUMIF

[lkjing]

Hi...I can't seem to use the SUMIF function to calculate a criteria. Despite thorough checks through the formula, the function keeps returning zero for the answer. I am using two linked spreadsheets. One is for the data and the other is for the report. In the data spreadsheet, A1:A5 for example have several data like buyer, order quantity...While B1:B5 have the necessary inputs. I tried to use the formula SUMIF to calulate Order Quantity in the report spreadsheet to no avail. The Formula is I'm using is something like this SUMIF(A1:A5;=Order Quantity;B1:B5). Can anybody help? Thanks

 

[lkjing]

I can't use words for tt? I'm goin to repeat the data format in A1:A5 down the column so i hope to use SUMIF to calculate the total amount of OT.

 

[Fergus]

Hi lkjing,

From your original post your Data Sheet sounds like it looks like the following. If so, what is it you are trying to get onto your report sheet?

Book3
	A	B	C	D
1	Buyer	JoeBloggs
2	OrderQuantity	1000
3	Orderdate	1/01/2005
4	DispatchDate	15/01/2005
5	AreaCode	ST170DP
Data Sheet

 

[Fergus]

Hi again,

Guessing from your last post, does your Data Sheet look like this?

Book3
	A	B	C	D
1	Buyer	JoeBloggs
2	OrderQuantity	1000
3	Orderdate	1/01/2005
4	DispatchDate	15/01/2005
5	AreaCode	ST170DP
6	Buyer	SnowWhite
7	OrderQuantity	1500
8	Orderdate	2/01/2005
9	DispatchDate	6/01/2005
10	AreaCode	xxxyyy
11	Buyer	Pinnochio
12	OrderQuantity	5300
13	Orderdate	12/12/2004
14	DispatchDate	25/12/2004
15	AreaCode	abc123
16	Buyer	PoohBear
17	OrderQuantity	2000
18	Orderdate	14/01/2005
19	DispatchDate	20/01/2005
20	AreaCode	kkk678
Data Sheet

If so maybe this is what you want on your Report Sheet:

Book3
	A	B	C	D
1	TotalOrders:	9800
Report

Formula in Cell B2 on Report Sheet is : =SUMIF('Data Sheet'!A1:A20,"Order Quantity",'Data Sheet'!B1:B20)

Does that help?

 

[lkjing]

yeah it's exactly the same..but i tried using similar formula but the answer returned a ZERO...It doesnt quite add up...Or is something to do with the cell format though I did not do anything to it...

 

[Fergus]

Hi lkjing,

The formula you quoted in your first post was: =SUMIF(A1:A5;=Order Quantity;B1:B5).

compare that with the one I gave you: =SUMIF('Data Sheet'!A1:A20,"Order Quantity",'Data Sheet'!B1:B20)

Forgetting the 'Data Sheet'! bit, which was necessary because the formula was on your other linked Report Sheet, you will see that:

1) Order Quantity needs quotes around it because it's text,
2) No need for the = sign
3) you had semi-colons where you should have had commas

All of which add up to an incorrect formula :wink:

 

[Fergus]

As an after thought - if you don't know how long your Data Sheet will get as you add further data you can write the formula as : =SUMIF('Data Sheet'!A:A,"Order Quantity",'Data Sheet'!B:B)

 

[lkjing]

So how do i go about using the "Data Sheet" ? I mean do i juz type it in? or do i have to name the whole list as "Data Sheet"?

 

[lkjing]

Hi Fergus..
I Don't exactly catch what you mean..What do you mean by OT needs quotes around it? Thanks

 

[Fergus]

Hi again,

I assumed your data sheet was named Data Sheet and your report was in a separate worksheet called Report. You can re-name worksheets by right clicking on the sheet tab (the tab at the bottom which when you first open a new workbook are called Sheet1, Sheet2 etc.)

And Yes Order Quantity needs to be written the same as it is written on the Data Sheet with quotes around it - i.e. "Order Quantity" - as shown in my formula. Without quotes it will return an answer of zero even if all other parts of the formula are correct as described above.

You can copy formulas directly off posts on this Board by highlighting, right clicking and selecting copy. Then click on the cell in your worksheet where you want them, right clicking and selecting paste.

Is that enough to be going on with?

 

[lkjing]

I will try it later...Thanks for your help once again