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

clindstrom

New Member
Joined
Sep 25, 2014
Messages
2
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:
ABCDE<--D Yellow, E Blue
1<--Yellow pair
1<--Blue pair
<--White pair
11<--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
DATEVALUEOTHERQUANTITY
1-SepCatasdoiu1
1-SepDogagdj1
2-SepDogasd2
2-SepFishjfsdfh1
2-SepCatasrafhb1
2-SepCatzxcva3
2-SepOtherrfyhyfd14
2-SepDogzxcv1
3-SepFishtreat1
4-SepOtherzzzdf1
4-SepOthergraez2
4-SepBirdzfgzry1

<tbody>
</tbody>
WorksheetB
DATECatDogFishBirdOther
1-Sep11000
2-Sep431014
3-Sep00100
4-Sep00013
5-Sep00000
6-Sep00000

<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!
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

azumi

Well-known Member
Joined
Jun 4, 2013
Messages
555
Maybe, put in F2 and copied down

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

clindstrom

New Member
Joined
Sep 25, 2014
Messages
2
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
55,039
Office Version
  1. 365
Platform
  1. Windows
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
DE
1
21
31
4
511
6
CF Pairs
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D21. / Formula is =$D2&"|"&$E2="1|1"Abc
D22. / Formula is =$D2&"|"&$E2="|1"Abc
D23. / Formula is =$D2&"|"&$E2="1|"Abc
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,109
Messages
5,857,441
Members
431,879
Latest member
KiwDaWabbit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top