Conditional statements

Thank you very much for your help Paul. The solution, which I don't fully understand, works brilliantly!


Hello,

A little while ago a posted a question in this forum and I received a very helpful answer. Thank you for that; it was very much appreciated. I hope that one of you Excel geniuses out there can help me again.

Attached you should find a simplified version of the problem I'm facing. I have a large number of product data that needs to be codified according to different categories. Specifically, there are various products in 3 different rooms (room 1, 2 and 3 see column A). Each product has a description (column B) which results in a manually assigned value that determines the code of the product (code 1, 2, 3 or 4 see Column C). The "results table" needs to count each product according to its code but by taking into account the room that the product is in. For example, in F9 the value 2 was derived by counting the occurrences of products in room 1 which were assigned code 1. Similarly H11 shows 0 products with code 3 in room 3. Please note that if necessary I could use absolute cell positions.

Is there a formula that will enable me to do these ‘calculations’? Using pseudo code I'd write something like this:

IF (code = 1 and room 1 = true) then add 1 to counter in F9 else
IF (code = 1 and room 2 = true) then add 1 to counter F10 else
etc.

I really hope this makes sense to someone out there who's willing to spare a few minutes to work out a solution. It goes without saying that I haven't been able to come up with anything that works and doesn't involve 50 different temp values being used as 'flags' in different cells!

Many thanks

Hi Gigaworks,

In F9 try the following formula:

=SUMPRODUCT(--($A$2:$A$16=$E9),--($C$2:$C$16=RIGHT(F$7,1)+0))

Copy that to the right and down. In your spreadsheet make sure the values in column A match EXACTLY the values in column E (some of yours don't, for example there is a space after all of the "Room 3" entries in column A, but no space where it says "Room 3" in E11).

The second part of the formula looks at the last character in row 7 (1, 2, 3 or 4) to match it against the code in column C. This may need to be adjusted for your not-as-simplistic real dataset.