Excel Query date range for Value

compguy101101

Trying to query an excel document to determine the number of times a value appears so I can put in conditional formatting on and heat map to determine the number of times the resource is busy during a date.

I have Jan1-Dec31 in individual cells I need to query data split among three colums as follows

       A       B       C
      Jan1    Jan12   Bob
      Jan2    Jan10   Roger
      Jan11   Jan14   Bob

The formula needs to use the start date in column A and end date in column B and count the resource name occurrence in column C.

So my output would go into my Heat Map as follows.

        Bob  Roger
 Jan1    1    0

 Jan2    1    1

 Jan3    1    1

 Jan4    1    1

 Jan5    1    1

 Jan6    1    1

 Jan7    1    1

 Jan8    1    1

 Jan9    1    1

 Jan10   1    1

 Jan11   2    0

 Jan12   2    0

 Jan13   1    0

 Jan14   1    0

If if could expanded without having to make 365 formulas for each resource that would be great.

Joe

I think I'm understanding your question properly. The way I'm interpreting it is that you're looking for a way to expand your "heat map" input, but I may be reading it wrong. Let me know if I'm mistaken.

This is a classic case of where SUMPRODUCT is awesome.

=SUMPRODUCT(--($E2>=$A$2:$A$4)*($E2<=$B$2:$B$4)*(F$1=$C$2:$C$4))

Which you can copy over and down (see image). You'll obviously need to edit your ranges accordingly to fit your real data.

It works like this. Consider Bob's 1/1/2016. It takes the date and compares the date to the start date, and creates an array of true/false based on whether or not the date is greater than or equal to the start dates in the start date array. Then it does the same, less than or equal to the end dates in the end date array. Then it checks the resource array for Bobs. At the end, you have three arrays:

{TRUE, FALSE, FALSE} * {TRUE, TRUE, TRUE} * {TRUE, FALSE, TRUE} -> {TRUE, FALSE, FALSE}. The * is an AND operator, so any place where TRUE, TRUE, TRUE occurs is a 1 and if any FALSE occurs its a zero. Then, it coerces your TRUE, FALSE, FALSE array to 1, 0, 0 and sums the result!

Edit: Here's a way to tackle what we're talking about in the comments.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-03-27

Comments

0 comments

TOP Ranking

Article

Excel Query date range for Value

Excel Query date range for Value

Loopback Error: connect ECONNREFUSED 127.0.0.1:3306 (MAMP)

Can't pre-populate phone number and message body in SMS link on iPhones when SMS app is not running in the background

pump.io port in URL

How to import an asset in swift using Bundle.main.path() in a react-native native module

Failed to listen on localhost:8000 (reason: Cannot assign requested address)

Spring Boot JPA PostgreSQL Web App - Internal Authentication Error

Emulator wrong screen resolution in Android Studio 1.3

3D Touch Peek Swipe Like Mail

Double spacing in rmarkdown pdf

Svchost high CPU from Microsoft.BingWeather app errors

How to how increase/decrease compared to adjacent cell

Using Response.Redirect with Friendly URLS in ASP.NET

java.lang.NullPointerException: Cannot read the array length because "<local3>" is null

BigQuery - concatenate ignoring NULL

How to fix "pickle_module.load(f, **pickle_load_args) _pickle.UnpicklingError: invalid load key, '<'" using YOLOv3?

ngClass error (Can't bind ngClass since it isn't a known property of div) in Angular 11.0.3

Can a 32-bit antivirus program protect you from 64-bit threats

Make a B+ Tree concurrent thread safe

Bootstrap 5 Static Modal Still Closes when I Click Outside

Vector input in shiny R and then use it

Assembly definition can't resolve namespaces from external packages