Excel Question - SUMIFS

Excel Question - SUMIFS

Didn't find your answer?

Hi,

I am trying to sum a column of values if two criteria are met but it is not working...

=SUMIFS('Kit List'!$H$5:$H$50,'Kit List'!$F$5:$F$50,$A$4,'Kit List'!$A$4:$A$49,B4)

I want to sum Kit List H5:H50 if

Condition 1: Kit list F5:F50 is equal to cell A4

and

Condition 2: Kit list A4:A49 is equal to cell B4

I have two worksheets,

First is Kit List, which has Month value in Column A , Variable in Column F and the costs that need to be summed in column H

Second is Payments, I have Supplier in A4 and the month value in B4.

Any help would be very appreciated!

Thanks

Replies (17)

Please login or register to join the discussion.

avatar
By ACDWebb
09th Jul 2012 15:05

So

your supplier in the 'Kit List' sheet is in Col F and you are trying to add to sums on the Payments sheet and by ref to the Supplier name shown on the payments sheet in $A$4 and month in B4

The supplier will always be what is in A4, but the month will move as you copy the SUMIFS down the sheet

Perhaps try specifying the Payments sheet name for the criteria:

=SUMIFS('Kit List'!$H$5:$H$50,'Kit List'!$F$5:$F$50,'Payments'!$A$4,'Kit List'!$A$4:$A$49,'Payments'!B4)

Thanks (0)
avatar
By Lorraine Meeks
09th Jul 2012 15:24

Hi,

 

Thanks for your reply, but still no joy.  Just keeps returning 0 for everything...

Thanks (0)
avatar
By ACDWebb
09th Jul 2012 15:42

How are you setting the Supplier name?

If, for example, something you were doing in the variable in F on the Kit list sheet meant that there was a leading, or trailing " " that was not matched in 'Payments'!$A$4 the answer will be 0 as there would be no match - because "Supplier" and "Supplier " do not match

Thanks (0)
avatar
By Lorraine Meeks
09th Jul 2012 15:53

The Variable in F definately matches the cell A4.

Thanks (0)
avatar
By Cantona1
09th Jul 2012 19:46

Lorraine!

I did get a value. Used the same formula as ACDWebb:

You could try the following idea:

First:

Sometimes, SUMIFS does not work if you type a formual as you might forget to include "" in the criteria range, so it would be good idea if you enter the formula using "The Insert Function". If you are unable to see the insert function on formula bar, go to formulas and pick up the SUMIFS function. Do not type anything just link the function arguments with your data as excel asks you the sum range and criteria. I had tried it in the past and worked.

Second:

There may be a problem with your date criteria.

Try to use DateValue function before the date and or the put the dates in " ".

 

Thanks (0)
avatar
By Cantona1
10th Jul 2012 21:43

=SUMIFS('Kit List'!$H$5:$H$50,'Kit List'!$F$5:$F$50,$A$4,'Kit List'!$A$4:$A$49,B4

is a wrong formula anyway. You are picking up your criteria from the same sheet name. The criterai for B4 should come from another sheet name( I think Payments)

Thanks (0)
Replying to johngroganjga:
avatar
By ACDWebb
16th Jul 2012 15:48

I think that

Cantona1 wrote:

=SUMIFS('Kit List'!$H$5:$H$50,'Kit List'!$F$5:$F$50,$A$4,'Kit List'!$A$4:$A$49,B4

is a wrong formula anyway. You are picking up your criteria from the same sheet name. The criterai for B4 should come from another sheet name( I think Payments)

so long as the formula is on the Payments sheet then Excel assumes B4 to equal 'Payments'!B4

It must be one of the criteria is not getting a match. To narrow it down try splitting the formula to see what results you get for both, so in different cells put:

=SUMIFS('Kit List'!$H$5:$H$50,'Kit List'!$F$5:$F$50,$A$4) - to test the Month part

=SUMIFS('Kit List'!$H$5:$H$50,'Kit List'!$A$4:$A$49,B4) - to test the Supplier part

If you get an answer for the month but not the supplier, then your problem is the supplier part of the formula, and vice versa.

Thanks (0)
By sysmod
13th Jul 2012 16:37

Test

As 'new' said, break it down to small parts to test.

First check that matches exist

=MATCH($A$4,'Kit List'!$F$5:$F$50,0)

=MATCH(B4,'Kit List'!$A$4:$A$49,0)

btw why not $B$4 ?

 

Thanks (0)
avatar
By SteveRadcliffe
13th Jul 2012 17:05

Suggestion

Hi

I have reproduced this and the formula works fine for me. Typing it in rather than using the function button (Fx).

However, if there is a space after the month or somewhere in one of the cells, the sumif doesn't pick up the number.

For example, I tried it using 'jan' (which worked) and then 'jan ' on one of the kitlist lines and it dropped the amount from the sumif total.

Hopefully this makes sense.

Steve

Thanks (0)
avatar
By gsgordon
13th Jul 2012 17:20

Works for me

I inserted the formula from the Formulas tab in Excel 2007, which gives the formula with no $ signs! 

The only way I get zero for the result is if the criteria are not met, e.g. for month B4 = March and supplier A4 = Alpha, all the months that are March in column A of 'Kit List' have suppliers in column F that are not Alpha. This is the correct answer!

I also agree with the previous post that getting the correct values in (all) the cells is vital.

PS: The OP's description does not necessarily involve copying down since the only criteria mentioned are in specific cells A4 & B4. However, if there are different values of the criteria in A5 & B5 etc, and the problem then requires the SUMIFS formula to be copied down, then suitable $ signs will be required.

Thanks (0)
avatar
By sayersdave
19th Jul 2012 10:58

Try Something like

 

=sumproduct(('Kit List'!$H$5:$H$50=$A$4)*('Kit List'!$A$4:$A$49=$B$4)*'Kit List'!$H$5:$H$50)

Without seeing the spreadsheet it's difficult to give the best advice, but hopefully that will work.

 

 

Thanks (0)
avatar
By lynamn
19th Jul 2012 11:22

5:50 v 4:49

Your 3 ranges on Kit List don't match.  2 refer to rows 5 to 50; the 3rd to rows 4 to 49.

Thanks (0)
avatar
By gsgordon
19th Jul 2012 11:31

They don't need to match

In my post above, I used the same ranges as the OP. I believe the ranges just have to be the same length.

Thanks (0)
avatar
By lynamn
19th Jul 2012 11:56

I agree, but ...

The wording of the original question suggests that this is a table, where you would expect them to be aligned.  I've experienced struggling to see why I was getting the wrong results that turned out to be due to that type of misalignment.

Thanks (0)
avatar
By sjt303
19th Jul 2012 15:20

Try an array

Lorraine, my understanding is that you have two sheets

 

1. Kit List:

Colum A contains a month value

Column F contains an unspecified variable, but I think you mean supplier name

Column H contains the costs that need to be aggregated

 

2. Payments

Column A contains supplier name

Column B contains the month

 

I think in the Payments sheet you want to find a match for each supplier and month value listed in columns A and B respectively.

 

You will need to be careful by what you mean by “month value” and ensure that the column A in Kit List and column B in Payments follow the same format otherwise criteria matching will not work.

 

Now, setting aside you can get the desired results by making an appropriate pivot table of the data in Kit List, you can also apply array formula, which I find more flexible than the conditional formula in Excel.  You will need to ensure that the data range to be matched to your formula is the same size and row locations for each criteria.

 

So assuming in Kit List your month values are in cells A5:A50, your supplier values are in F5:F50, and your costs are in cells H5:H50; and the first month and supplier in Payments appear in row 5, in cell C5 in Payments enter

 

=SUM(IF('Kit List'!$A$5:$A$50=Payments!A5,IF('Kit List'!$F$5:$F$50=Payments!B5,'Kit List'!$H$5:$H$50,0)))

 

Do not hit ENTER after keying the formula, but hold down SHIFT and CTRL together and hit ENTER.  You will notice curly brackets around the formula if you view it from the formula window, like this

 

{=SUM(IF('Kit List'!$A$5:$A$50=Payments!A5,IF('Kit List'!$F$5:$F$50=Payments!B5,'Kit List'!$H$5:$H$50,0)))}

 

This indicates to Excel to treat the formula as an array.  The formula can then be cut and pasted in the normal fashion for each row in Payments.

 

Hope this helps!

Thanks (0)
avatar
By Cantona1
19th Jul 2012 22:07

There are over 15 replies to this thread so far and yet the person who asked the question has not commented. It is weird. I think we should have a button called "Closed thread"

Thanks (0)
avatar
By gsgordon
20th Jul 2012 12:05

Agreed but ...

Maybe they are on holiday, or their default setting for getting emails with new comments is set to Never!

Thanks (0)