simplest way to find a cell value based on two or more text criteria?

[hannahmc8]

Hi,
What is the best way to find a cell value based upon two text based criteria?
This is a simplified version of my table which has a week's worth of dates, 10 tills and multiple cash items per till.
-------A--------B--------C-----------D-----------E------------------F----------
--1---TILL----DATE----CASH--FIND TILL---FIND DATE---RETURN ANSWER
--2----X-------1/JAN----£5
--3----Y-------1/JAN----£10
--4----Y-------1/JAN----£10
--5----Y-------2/JAN----£20
--6----Z-------no value--no value
I want to find the cash per till per date e.g
Info needed 1. On the 1st January Till x returned £5
Info needed 2. On the 1st January Till y returned £20 (£10+£10)
Info needed 3. On the 1st January the total of all tills returned £25 (£5+£10+£10)
I have used an INDEX and MATCH array which finds one match but does not sum multiples...
Many many thanks
Hannah

 

[JamesW]

Hi,

Try:

=SUMPRODUCT(--($D$2=$B$2:$B$6),($E$2=$A42:$A$6)*($C$2:$C$6))

 

[JamesW]

Edit timed out

Use this to exclude "No Value":

=SUMPRODUCT(--($D$2=$B$2:$B$6),--($E$2=$A$2:$A$6),--($C$2:$C$6<>"No Value"),$C$2:$C$6)

 

[hannahmc8]

Thank you for the reply!
It is returning a value of 0?

 

[hannahmc8]

I have both working thank you - the D cell was referring to the B column not the A column and vice versa!
Could you explain what the function of the -- are in the formula?
Intrigued and trying very hard to learn!
Hannah

 

[JamesW]

Hi,

Happy you got it working.

The -- is a double unary operator. Basically it converts a BOOLEAN value (TRUE/FALSE) to a NUMERIC value (1 or 0). You can also do this by multiplying by 1, but -- is the fastest way.

This may be of use to you:

http://www.mrexcel.com/forum/showpost.php?p=363710&postcount=4

 

[hannahmc8]

James you are being a star.
I have transferred the formula to the table I am using and am now frustratingly getting the VALUE warning. Each of the column references contain empty cells which are necessary for further data entry.....
Thank you for your time
Hannah

 

[JamesW]

Hi Hannah,

It shouldn't matter about empty cells.

I am using this as a test:

=SUMPRODUCT(--($A$2:$A$100=$D$2),--($B$2:$B$100=$E$2),$C$2:$C$100)

Where:

A2:A100 is your list of till names.
B2:B100 is your list of dates.
C2:C100 is your list of cash income.
D2 is the Till lookup value.
E2 is the date lookup value.

I have blanks, data and "No Value" in random cells but it is still calculating the correct result.

Can you paste the formula exactly?

 

[hannahmc8]

=SUMPRODUCT(--($C$9=$H$20:$H$23),($C$11=$C$20:$C$23)*($K$20:$K$23))

this is the actual formula. All of the cells in row 23 are blank and are giving a VALUE error, but if i change the 23 to 22 where each cell in the row has a value it works
I may throw my mac out of the window!

 

[JamesW]

Try changing it to:

=SUMPRODUCT(--($C$9=$H$20:$H$23),--($C$11=$C$20:$C$23),$K$20:$K$23)