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.
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)
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
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 " ".
=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)
I think that
so long as the formula is on the Payments sheet then Excel assumes B4 to equal 'Payments'!B4=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)
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.
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 ?
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
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.
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.
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.
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.
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.
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!
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"
Agreed but ...
Maybe they are on holiday, or their default setting for getting emails with new comments is set to Never!