Counting a totaling column?

tmichelle068

I have a form that people can submit multiple observations, and a total count column to count those submissions since they can have up to 5 on each line. I want to count the area that submitted the observation plus that amount in the count column. How can I work that formula? Here is what I have for now, which isn't working;

=COUNTIFS({Unit}, "B2 - SICU", {Count}, > 0))

Genevieve P.

Hi @tmichelle068

Are you looking to COUNT or SUM the values? It sounds like you may want to use SUMIFS instead of COUNTIFS, so that the numbers in your COUNT column are added together. Try this:

=SUMIFS({Count}, {Unit}, "B2 - SICU")

If this doesn't work, it would be helpful to see a screen capture but please block out sensitive data.

Cheers,

Genevieve

tmichelle068

Thanks for your response! Unfortunately, that doesn't work for the information I am trying to gather. I have included a screenshot. For each line they can have up to 5 observations on a row, so totaling the Unit column will not give accurate information. I need the Total Count column and the Unit name to be calculated per month.

For example: I can see for March that A6 has submitted 13 observations, but I need a formula to calculate that so it can show on a dashboard and update as there are more observations added.

Genevieve P.

Hi @tmichelle068

Ah! Thank you for clarifying. So we don't need to look at the "Total Count" column at all - you're just looking to see how many times the value "xxx" appears in the Unit column in a specific timeframe, is that correct?

If so, then yes! We would use COUNTIFS.

=COUNTIFS({Unit}, "B2 - SICU")

Your original formula would only look for the Unit if the Count is greater than 0. The reason this one didn't work is because you have two )) at the end instead of just one )

You can try this, but it won't have a date range associated:

=COUNTIFS({Unit}, "B2 - SICU", {Count}, > 0)

If you then want to include a month, you can use the MONTH function like this for March:

=COUNTIFS({Unit}, "B2 - SICU", {Count}, > 0, {Date Column}, IFERROR(MONTH(@cell), "") = 3)

Cheers,

Genevieve

tmichelle068

Sorry, I don't think I explained that properly 😊 Here's what I'm trying to do:

I need to see how many times A9 submitted something for March BUT I also need it to add together the amounts in the total count column. So for the snip below, I can see A9 submitted 8 times BUT their total submissions are 12 per the total count column. I am trying to make a formula to do that calculation for me so I can show it to them without them having to filter through this sheet.

Genevieve P.

Hi @tmichelle068

No problem! Then we go back to the SUMIFS function 🙂

Either:

=SUMIFS({Count}, {Unit}, "B2 - SICU")

or with a month specified as well

=SUMIFS({Count}, {Unit}, "B2 - SICU",  {Date Column}, IFERROR(MONTH(@cell), "") = 3)

See: SUMIFS Function