Formula to grab data for a specific date and consecutive dates in the past

Connie Torres

I'm looking for a formula that will grab the data {ratio} for a specific {facility} on a specific day {Modified by}, and over the course of a 10 day period to turn it all into a chart.

We currently enter data daily for ratios at several locations and have a column for modified date. I would like to turn this into location rows with columns for the ratio on each of the last 10 days. What formula would you suggest I can use to then chart ratios in columns for today and the last 12 days (Filtering by 12 days because we skip the weekends)

Any good suggestions? Thanks.

Connie Torres

Update - My sister, the excel geek, helped me realize my ratio range needed to be at the front of the formula, not the end. This solved what I was looking to gain.

=SUMIFS({Ratios Range 1}, {Location}, [Primary Column]@row, {Date1}, =TODAY())

Genevieve P.

Hi @Connie Torres

Are you looking for a SUM within the date range for each Facility? Or do you want to keep it parsed out by rows?

I would suggest using a Report! You could Filter by the Date range you'd like, then Group by Facility, and Summarize using SUM on the Patient Ratio column.

See: Redesigned Reports with Grouping and Summary Functions

You can use a Grouped/Summarized Report as the source for a Chart.

If you're looking for the individual row numbers to put on a Chart, you could instead create one Report per Facility, filtering by both date range and Facility. Then use this as the source.

Let me know if this makes sense and will work for you!

Cheers,

Genevieve

Connie Torres

Thanks Genevieve. I'm not particularly looking for a sum. I'm trying just to pull the data point from each specific day for a location to show their data points on a graph.

My report is filtered, but it adds all the data in the chart without being able to identify individual locations.

I am not looking for a summary, but the individual points for each day.

I did try a report for each, but cannot select multiple reports to populate a graph on the dashboard.

I’m looking for a line graph like this – but includes all locations, not just the one

I have a formula that works to select the numbers by date that does work:

=SUMIF({Date1}, =TODAY(), {Ratios Range 1})

And I have a formula that works to select the specific locations:

=SUMIF({Location Ratios Range 2}, [Primary Column]@row, {Ratios Range 1})

I’m trying to get a formula that will count the ratio, for a specific location on a date, but each combination I’ve tried comes up with errors:

=SUMIFS({Location Ratios Range 2}, [Primary Column]@row, {Date1}, =TODAY()), {Ratios Range 1})= #UNPARSEABLE

=IF(AND({Location Ratios Range 2}, [Primary Column]@row, {Date1}, =TODAY()), {Ratios Range 1})=#INVALID DATA TYPE

=SUMIFS(AND({Location Ratios Range 2}, [Primary Column]@row, {Date1}, TODAY()), {Ratios Range 1})=#INCORRECT ARGUMENT

Connie Torres

Update - My sister, the excel geek, helped me realize my ratio range needed to be at the front of the formula, not the end. This solved what I was looking to gain.

=SUMIFS({Ratios Range 1}, {Location}, [Primary Column]@row, {Date1}, =TODAY())

Genevieve P.

Hi @Connie Torres

My apologies for the delay! I'm glad you were able to get the formula working 🙂

Is it all sorted or is there anything else you need help with to get those charts going?