Ask Your Question
0

Calc: can you combine SUMIF and VLOOKUP?

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

ChrisCrinkle gravatar image

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

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
1

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

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(SUMIFS(Staff.$B$1:$B$19;Staff.$A$1:$A$19;$B$3:$B$13))
sumproduct is to avoid enter the formula as array, and sumifs because it's easy to add new criteria if needed.

For average:
=SUMPRODUCT(AVERAGE(IF($B$3:$B$13<>"";AVERAGEIFS(Staff.$C$1:$C$19;Staff.$A$1:$A$19;$B$3:$B$13))))

For sum of average sales:
=SUMPRODUCT(SUMIFS(Staff.$C$1:$C$19;Staff.$A$1:$A$19;$B$3:$B$13))

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

edit flag offensive delete link more
0

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

ChrisCrinkle gravatar image

Wow that SUMPRODUCT just blew my brains out. Thanks!

edit flag offensive delete link more

Comments

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 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 55 times

Last updated: Jun 12