Ask Your Question

Calc: can you combine SUMIF and VLOOKUP?

asked 2018-06-03 18:29:35 +0100

ChrisCrinkle gravatar image

updated 2018-06-03 22:00:56 +0100

Ok I have column A1-A32 with a list of different items in them, sometimes identical. On a Different sheet called "Bob" there are cells A1-H32, which contain various data for each type of item some numerical.

I also have the intention of making a Horizontal month to month list (e.g Column A is this month, B will be next month etc), so i can't utilise the rest of row A for it's VLOOKUP details and then total each column at the bottom. I really want the sum total of various lookups added as one column, under the list (say, cell A35), and in one cell.

Is it possible to use SUMIF, with a VLOOKUP which takes it's first argument as being the contents of the cell the sum if is on? If we assume, in the function proposition below, ## represents, for the lookup, the cell the SUMIF function is currently on, I basically am wondering if something like this is possible:

=SUMIF(A1:A32,VLOOKUP(##,$Bob.A1:A32,3,FALSE),<>"") The <>"" would hopefully make it ignore empty cells as sometimes some cells in the column will be empty.

EDIT: See here for an example, The red cell is where i need the formula and to the side is instructions of what i want to happen. C:\fakepath\Example.ods

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2018-06-04 01:13:42 +0100

m.a.riosv gravatar image

Not clear to me what's the meaning of total skill level, the sum of them can be obtained with:
sumproduct is to avoid enter the formula as array, and sumifs because it's easy to add new criteria if needed.

For average:

For sum of average sales:

Attached the file with the formulas C:\fakepath\15280559961189898.ods

edit flag offensive delete link more

answered 2018-06-12 20:22:15 +0100

ChrisCrinkle gravatar image

Wow that SUMPRODUCT just blew my brains out. Thanks!

edit flag offensive delete link more


This is not an answer, so it should be moved to a comment. See guidelines for asking.

Jim K gravatar imageJim K ( 2018-06-13 00:47:09 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-06-03 18:29:35 +0100

Seen: 2,370 times

Last updated: Jun 12 '18