sheet summary and I need several formulas

I have a sheet summary and I need to have several formulas

1-How many requests total? =COUNT([Date Received]:[Date Received])

2-How many requests from Group "A"-Group "B"?

a) which has the most volume?

b) what formula to use percent as well

3-How many actual staff has been requested? (FYI- there are 6 columns for group A & B with staff requests and another 6 columns for group A & B that has the actual number of staff provided)

a) How many have been fulfilled/provided?

b) what formula to show the percent.

4- formula to count "Approved" =COUNTIF([Status (IMT) ONLY]:[Status (IMT) ONLY], "Approved")

a) how do I edit the formula to look for " Approved & Already Fulfilled"?

b) what formula to use percent of total approved/Already fulfilled compared to other statuses in the column?

Find more posts tagged with

Accepted answers

Genevieve P.

Hi @Bobby1

Thank you for this additional information!

I'll explain how to do one of the calculations and then you should be able to replicate this for your other needs.

"how to compare total requests in light green to whats provided total in dark green?"

To do this, you can SUM together all the Light Green columns in one field.
Then you can create a second Summary field and SUM together all the Dark Green columns.
Finally, you can take the Total of Dark / Total of Light to get the percent fulfilled.

For example, this is how you can SUM 6 columns:

=SUM([Column 1]:[Column 1], [Column 2]:[Column 2], [Column 3]:[Column 3], [Column 4]:[Column 4], [Column 5]:[Column 5], [Column 6]:[Column 6])

Screen Shot 2022-01-21 at 9.45.03 AM.png

Once you have two fields with a total SUM, you can use this to find the percent:

Screen Shot 2022-01-21 at 9.46.23 AM.png

And format the Field to be a Percent:

Screen Shot 2022-01-21 at 9.46.37 AM.png

Cheers!

Genevieve

Genevieve P.

Hi @Bobby1

Thank you for the additional screen captures; I can see that your Status fields are either "Approved" or "Already Fulfilled", not one status that says "Approved & Already Fulfilled".

In this case, you can add 2 separate COUNTIFS statements together:

=COUNTIF([Status (IMT) ONLY]:[Status (IMT) ONLY], "Approved") + COUNTIF([Status (IMT) ONLY]:[Status (IMT) ONLY], "Already Fulfilled")

Then for your second question, you can add all 4 COUNTIF statements together:

=COUNTIF([Status (IMT) ONLY]:[Status (IMT) ONLY], "Approved") + COUNTIF([Status (IMT) ONLY]:[Status (IMT) ONLY], "Already Fulfilled") + COUNTIF([Status (DAS) ONLY]:[Status (DAS) ONLY], "Approved") + COUNTIF([Status (DAS) ONLY]:[Status (DAS) ONLY], "Already Fulfilled")

All comments

Genevieve P.

Hi @Bobby1

It would definitely be helpful to see a full screen capture of your sheet and all the columns that may be referenced in your formula to be able to accurately build out suggested formula structures.

Here are my initial suggestions based on the information provided:

1) How many requests total?

It looks like you figured this one out already? You can use a COUNT formula to count the number of rows that are not blank, based on a specific column. See: COUNT Function

=COUNT([Column Name]:[Column Name])

2) How many requests from Group "A"-Group "B"?

For these instances we can use a COUNTIF Function to Count the number of rows in one column that have a specific criteria. For example:

=COUNTIF([Group Column]:[Group Column], "Group A")

=COUNTIF([Group Column]:[Group Column], "Group B")

a) which has the most volume?

Then once you have two values from your two formulas, you can reference Sheet Summary cells to see which one is bigger. See: Take Full Advantage of Sheet Summary: Tips and Best Practices

For example, if your first formula counting Group A is in a field called "Group A Count" in your summary, you can reference this in a formula by [Group A Count]#

Then you can use an IF statement to see if the Count is greater in A or B.

=IF([Group A Count]# > [Group B Count]#, "Group A has more volume", "Group B has more volume")

b) what formula to use percent as well

For Percent, you already have the Total Count and the Count Per Group. That means you can simply divide one over the other. This will give you a decimal, so make sure you format your Summary Field to be a % format:

=[Group A Count]#/[Total Count]#

Note: if you title your fields differently you'll have to adjust the text in [these] to match your Summary Field titles.

3-How many actual staff has been requested? A & B

For this request it would be helpful to see a screen capture of your sheet in order to provide formula assistance.

4- formula to count "Approved" =COUNTIF([Status (IMT) ONLY]:[Status (IMT) ONLY], "Approved")

This looks correct!

a) how do I edit the formula to look for " Approved & Already Fulfilled"?

=COUNTIF([Status (IMT) ONLY]:[Status (IMT) ONLY], "Approved & Already Fulfilled")

b) what formula to use percent of total approved/Already fulfilled compared to other statuses in the column?

You can add together the two calculations you made above (the two COUNTIFS) by adding together the two summary fields. Then divide that by the total Count of how many Status cells are filled out in the entire sheet to find the percentage of those two status options.

=([Approved Summary Field]# + [Fulfilled Summary Field]#) / COUNT([Status (IMT) ONLY]:[Status (IMT) ONLY])

Here are some resources I would recommend reviewing as you continue to work on your data:

Formulas webinar series

SmarStart: Sheets

You may also want to look into Reports for some easy Grouping and Summary of data:

SmartStart: Reporting

Redesigned Reports with Grouping and Summary Functions

Cheers,

Genevieve

Bobby1

@Genevieve P. here are some screenshots

how to compare total requests in light green to whats provided toal in dark green?

how to get total of light blue requests compared to total of dark green provided?

Genevieve P.

Hi @Bobby1

Thank you for this additional information!

I'll explain how to do one of the calculations and then you should be able to replicate this for your other needs.

"how to compare total requests in light green to whats provided total in dark green?"

To do this, you can SUM together all the Light Green columns in one field.
Then you can create a second Summary field and SUM together all the Dark Green columns.
Finally, you can take the Total of Dark / Total of Light to get the percent fulfilled.

For example, this is how you can SUM 6 columns:

=SUM([Column 1]:[Column 1], [Column 2]:[Column 2], [Column 3]:[Column 3], [Column 4]:[Column 4], [Column 5]:[Column 5], [Column 6]:[Column 6])

Once you have two fields with a total SUM, you can use this to find the percent:

And format the Field to be a Percent:

Cheers!

Genevieve

Bobby1

@Genevieve P. Thank you for your help, light and dark colors are working great. I have run into a couple of issues

1-=COUNTIF([Status (IMT) ONLY]:[Status (IMT) ONLY], "Approved & Already Fulfilled") is giving me (0)-but =COUNTIF([Status (IMT) ONLY]:[Status (IMT) ONLY], "Approved") is giving me (12)

2- I would like to get the combined total between (das)(imt)

=COUNTIF([Status (IMT) ONLY]:[Status (IMT) ONLY], "Approved & Already Fulfilled") =COUNTIF([Status (DAS) ONLY]:[Status (DAS) ONLY], "Approved & Already Fulfilled")

Genevieve P.

Hi @Bobby1

Thank you for the additional screen captures; I can see that your Status fields are either "Approved" or "Already Fulfilled", not one status that says "Approved & Already Fulfilled".

In this case, you can add 2 separate COUNTIFS statements together:

=COUNTIF([Status (IMT) ONLY]:[Status (IMT) ONLY], "Approved") + COUNTIF([Status (IMT) ONLY]:[Status (IMT) ONLY], "Already Fulfilled")

Then for your second question, you can add all 4 COUNTIF statements together:

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.