Sum with Criteria

[Tommy1005]

I need help summing up a column based off of 4 criterias, but you cant just do a sumifs, that won't work for my case, because one criteria is based on what part number the company quoted on (which is one column), and then how much that part number is actually worth (which is a different column).

I attached a simple worksheet that highlights the two cells I'm trying to solve with a text block explaining exactly how I am trying to lay out the formula, and what the answer you. It is actually very straightforward, but I can't figure it out.

[Cutter]

Here is the way I would do it:
On your Dec tab in R5 add the heading 'Supplier'
In R6 and dragged down place this formula:

=VLOOKUP(B6,Inp!$B$3:$C$5,2,0)

Now, in cell I6 of your Sum tab place this formula (which produces result of 15,000):

=SUMIFS(Dec!$Q$6:$Q$8,Dec!$R$6:$R$8,I5,Dec!$M$6:$M$8,$D$6,Dec!$I$6:$I$8,$E$6,Dec!$J$6:$J$8,$F$6)

Drag this formula over to J6 and it results in 0.


You should change your profile to show that you are using a newer version of Excel

[Tommy1005]

What do you mean let me figure out the formula for J6? Shouldn't I just be able to take your formula and drag it across? If I'm wrong let me know, but I feel like that should work.

Thanks by the way, it worked perfectly for I6

[Cutter]

Sorry, I edited my post (because you were showing as being offline) to provide a formula that you could drag. The first formula I gave did not have anchors (and thus could not be dragged) so I made that comment.

So, the formula showing is to be dragged into J6.

[Tommy1005]

Sorry, I edited my post (because you were showing as being offline) to provide a formula that you could drag. The first formula I gave did not have anchors (and thus could not be dragged) so I made that comment.

So, the formula showing is to be dragged into J6.

Cutter I just realized, that this formula (although it works) I don't think I can use it, because there are some part numbers that have several companies quoting on. So the vlookup, will just show the first supplier and thats it.

IE: Part number 1, might have 4 companies quoting for it, but the vlookup will just display 1 company.

Is there a fix to this?

[Cutter]

I would say that you should list the bidding suppliers horizontally on the Inp sheet so that Supplier 2 for part 1 would be in cell D3, Supplier 3 for part 1 would be in E3, etc.

Repeat that setup on the Dec sheet and use VLOOKUP formulas that check for presence of a 2nd, 3rd, etc bidder.

If you have problems attach a revised file showing the anticipated extra considerations.

[Tommy1005]

I would say that you should list the bidding suppliers horizontally on the Inp sheet so that Supplier 2 for part 1 would be in cell D3, Supplier 3 for part 1 would be in E3, etc.

Repeat that setup on the Dec sheet and use VLOOKUP formulas that check for presence of a 2nd, 3rd, etc bidder.

If you have problems attach a revised file showing the anticipated extra considerations.

Damn, I won't be able to do that, because after this is all done, I'll be importing about 5000 suppliers, it will look very bad if each supplier name goes across the column

[Cutter]

I'm sure there will be a way to achieve what you want but you'll have to come up with a design that you can attach. Once you can do that we can look at it and someone will come up with a solution for you.

[Tommy1005]

I'm sure there will be a way to achieve what you want but you'll have to come up with a design that you can attach. Once you can do that we can look at it and someone will come up with a solution for you.

Okay I attached the file to this post, and I have text boxes explaining clearly what I am trying to find in the file.

The vlookup thing won't work because their are multiple companies that bid on the same part number, so each part number will have more than one company bidding on it.

See attached for the file I am trying to solve, it is very straight forward and easy to follow.

I am just trying to get a sum based off of 4 criterias and I cannot use sumifs or anything else that doesn't work on the standard version of Excel 2003.

Any ideas?

[daddylonglegs]

You can use this "array formula" in I6 (it's compatible with Excel 2003)

=SUM(IF(ISNUMBER(MATCH(Dec!$B6:$B8,IF(Inp!$C3:$C6=I5,Inp!$B3:$B6),0))*(Dec!$M6:$M8=$D6)*( Dec!$I6:$I8=$E6)*(Dec!$J6:$J8=$F6),Dec!$Q6:$Q8))

confirmed with CTRL+SHIFT+ENTER and copied across to K6, see attached

[Tommy1005]

thank you that worked. There are a few random hiccups, where its spitting out a random number for a supplier that should be at $0, but it's better than nothing.

Thanks again

[daddylonglegs]

There are a few random hiccups, where its spitting out a random number for a supplier that should be at $0, but it's better than nothing.

I wouldn't expect any problems like that - can you show an example?

[Tommy1005]

I wouldn't expect any problems like that - can you show an example?

See attached and look in the Summary tab in cell Q7, there's a number in there that I have no clue how it's getting there, it should be 0.

Also if you know how to make it so it doesn't show all the #DIV/0 Errors, I would greatly appreciate it, and the $ - and the $0,000.00

I just want it to be completely blank unless there is a number there.

[daddylonglegs]

Q7 is picking up the figure from Decode!Q6 because that row matches all criteria - supplier 9 is linked with HFT1 in row 60 of the input sheet.

To show zero as a blank change the cell format, for row 7 you have the format set to this

_("$"* #,##0.00_);_("$"* (#,##0.00);_("$"* "-"??_);_(@_)

The third element of that (in red) determines how zeroes are shown so you can just remove all that to display zero as blank, i.e.

_("$"* #,##0.00_);_("$"* (#,##0.00);;_(@_)

[Tommy1005]

Q7 is picking up the figure from Decode!Q6 because that row matches all criteria - supplier 9 is linked with HFT1 in row 60 of the input sheet.

To show zero as a blank change the cell format, for row 7 you have the format set to this

_("$"* #,##0.00_);_("$"* (#,##0.00);_("$"* "-"??_);_(@_)

The third element of that (in red) determines how zeroes are shown so you can just remove all that to display zero as blank, i.e.

_("$"* #,##0.00_);_("$"* (#,##0.00);;_(@_)

But if you look in row 60, only 2 of the 3 categories match. Row 60 is "Hoses" but it should only be matching it to "Fittings" on the Summary tab in cell E6.

EDIT: Also, I'm a bit confused with your method on showing blanks, I tried it but I am definitely doing it wrong as it keeps rejecting the formula and saying theres an error. Can you do it in the file I posted above and post it here so i can see an example?