Returning the sum of numerous cells based on multiple look up criteria.

**TheClaw2323** · 03-04-2016, 09:41 AM

So I have attached an example of what I'm after.

In the example I have the formula in cell G2 which is returning the sum of values in column D that match with the criteria in columns A, B & C. The specified criteria is in cells F2, F3 and H2. Whilst the data in columns A & B will only search against one criteria (F2 & F3), I want the formula to search the data in column C and return all values that match more than one criteria.

In the current example the formula returns £77 because that is the sum of D2 & D6. What I want it to do is return £144 because it would also sum D8 as the

Currently I am using a sumproduct formula but I suspect this may have to be changed.

This has been killing me and any help would be greatly appreciated!

Cheers,

Chris

**Kaper** · 03-04-2016, 09:52 AM

Try:

Formula:

Please Login or Register  to view this content.

**Kaper** · 03-04-2016, 09:56 AM

The key here is this part:

Formula:

Please Login or Register  to view this content.

it returns an array of boolena (true/false) values for each C2:C9 cell if CX is found in H2:H3 range, then respective element is true, otherwise it is false. And True/False is in multiplication treated as 1/0 resp.

**AlKey** · 03-04-2016, 10:04 AM

I would suggest a different approach. I have relocated A and B to a different location

Enter formula in G2, copy across and down

Formula:

Please Login or Register  to view this content.

v	A	B	C	D	E	F	G	H	I
1	Name	Role	Group	Income			A	B
2	Chris	Manager	A	£10.00		Manager	£10.00	£67.00
3	Sue	Coder	A	£4.00		Chris	£67.00
4	Chris	Tester	A	£7.00
5	Sue	Coder	B	£23.00
6	Chris	Manager	A	£67.00
7	Sue	Coder	B	£4.00
8	Chris	Manager	B	£67.00
9	Sue	Tester	A	£8.00

**AlKey** · 03-04-2016, 10:32 AM

But if you want just a total

Formula:

Please Login or Register  to view this content.

**TheClaw2323** · 03-05-2016, 10:23 AM

Thanks a million to both of you, both of those solutions work perfectly in my original spreadsheet too

Returning the sum of numerous cells based on multiple look up criteria.

LinkBack

Thread Tools

Rate This Thread

Display

Returning the sum of numerous cells based on multiple look up criteria.

Re: Returning the sum of numerous cells based on multiple look up criteria.

Re: Returning the sum of numerous cells based on multiple look up criteria.

Re: Returning the sum of numerous cells based on multiple look up criteria.

Re: Returning the sum of numerous cells based on multiple look up criteria.

Re: Returning the sum of numerous cells based on multiple look up criteria.

Thread Information

Users Browsing this Thread

Similar Threads

Writing a formula for returning a text value based on multiple criteria

[SOLVED] Returning multiple cells based on criteria of other cells

Returning multiple row based on criteria

2003 Excel: returning multiple values based on 2 criteria

Returning multiple rows of data based on certain criteria

Returning a lookup value based on multiple criteria.

returning entire row based on single or multiple criteria

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions