Looking for Help. Summing Cells in Different Locations Based on Multiple Variables. Bonus Easy Question, Too!

[clindstrom]

Hello, all! I've got two problems, one easy and the other not so much.

For starters, I just need two columns to show white if there is nothing in both cells on the row, yellow if there is a 1 in the left only, blue if there is a 1 in the right only and red if there is a number in both cells on the same row.

Example:
A	B	C	D	E	<--D Yellow, E Blue
			1		<--Yellow pair
				1	<--Blue pair
					<--White pair
			1	1	<--Red pair
^white^	^white^	^white^

<tbody>
</tbody>

Next, the harder one. This is what prompts me to post tonight:
I work with two sheets on a regular basis to track stuff that happens through the month. The first sheet is a record of events that happened including what date, a short description chosen from a dropdown list and the number of times that specific event happened. The second sheet tracks how many of each type of event happened based on the same short description and the date, totaled based on the quantity while ignoring the rest of the row. I want to automate this process and I know it is possible but it really does elude me after a few shifts of trying to crack it
To ensure consistency, these columns are all Data Validated with a warning about accuracy if there isn't a match.
Here is a fun stipulation: This report is shared among each shift over a database so all of the automation has to be part of the file itself.

Example of what I would like to see:

WorksheetA

DATE	VALUE	OTHER	QUANTITY
1-Sep	Cat	asdoiu	1
1-Sep	Dog	agdj	1
2-Sep	Dog	asd	2
2-Sep	Fish	jfsdfh	1
2-Sep	Cat	asrafhb	1
2-Sep	Cat	zxcva	3
2-Sep	Other	rfyhyfd	14
2-Sep	Dog	zxcv	1
3-Sep	Fish	treat	1
4-Sep	Other	zzzdf	1
4-Sep	Other	graez	2
4-Sep	Bird	zfgzry	1

<tbody>
</tbody>

WorksheetB

DATE	Cat	Dog	Fish	Bird	Other
1-Sep	1	1	0	0	0
2-Sep	4	3	1	0	14
3-Sep	0	0	1	0	0
4-Sep	0	0	0	1	3
5-Sep	0	0	0	0	0
6-Sep	0	0	0	0	0

<tbody>
</tbody>

So in short, I need the number of times each animal occurred each day from sheet A tallied onto the Grid on sheet B


Thanks for your tame!

 

[azumi]

Maybe, put in F2 and copied down

=IF(AND(D3<>"",E3<>""),"White",IF(OR(D3<>"",E3=""),"Yellow",IF(OR(D3="",E3<>""),"Red","")))

 

[clindstrom]

Maybe, put in F2 and copied down

=IF(AND(D3<>"",E3<>""),"White",IF(OR(D3<>"",E3=""),"Yellow",IF(OR(D3="",E3<>""),"Red","")))

I seem to have been misunderstood. My apologizes! I want the cell color to change based on the position of the 1

I hope that makes more sense

 

[Peter_SSs]

I seem to have been misunderstood. My apologizes! I want the cell color to change based on the position of the 1

I hope that makes more sense

Welcome to the MrExcel board!

Try this. Select from D2:Exx and apply the Conditional Formatting formulas as shown.

Excel Workbook
	D	E
1
2	1
3		1
4
5	1	1
6
CF Pairs

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2	1. / Formula is =$D2&"|"&$E2="1|1"	Abc
D2	2. / Formula is =$D2&"|"&$E2="|1"	Abc
D2	3. / Formula is =$D2&"|"&$E2="1|"	Abc