COUNTIFS

Hello,

Hope you can help me. How do you use Countifs formula using the table below?

1 ) I need the count per month, per name of the person who bought it. There's only two names, Mary and Joseph.

I have 4 individual sheets per raw materials. Date month column is listed mm/dd/yy

Sheet columns look like this below:

Date Rcvd |  Name of Person  | Purchased Amt | Booked 


2) I need to know Dollar Value Per month per person. I guess that would be another Metric.

Thanks.

Best Answer

Answers

  • Ramzi K
    Ramzi K ✭✭✭✭✭

    Hi @Marilen.Navarro103391,

    Hopefully I understand what you're trying to do. Try this:

    Expand your metric sheet to look like this so you have a section for each person. If you end up with more than two or three people you may want to rethink how your sheets are structured.

    . Then for each raw materials sheet add a "helper" column called Month and put this formula in it: =MONTH([Date Rcvd]@row) and make sure it's copied to all rows. Here's what this may look like in an example where I created a Pattern sheet:

    . Then add a row to your metrics sheet with the month numbers (see row 1 in my example above). You can hide this row later using a filter if you need to.

    . Then in the cell marked below with a red box, enter the following formula: =SUMIFS({Pattern Sheet - Purchase Amount Column}, {Pattern Sheet - Month Column}, January$1, {Pattern Sheet - Name of Person Column}, $[Primary Column]$3)

    Note: $[Primary Column]$3 refers to the cell with the name Joseph in it.

    . Copy the formula across all the months.

    . Then do the same for the other raw material sheets

    Notice the external references to the Pattern raw materials sheet. If you are not familiar with how to do external sheet references, let me know.

    . Finally, do the same for the ($) metrics section for each person using this formula: =SUMIFS({Pattern Sheet - Booked Column}, {Pattern Sheet - Month Column}, January$1, {Pattern Sheet - Name of Person Column}, $[Primary Column]$3) and copy it across as well.

    I hope all this makes sense.

    Cheers,

    Ramzi

    Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)

    Feel free to email me: ramzi@cedartreeconsulting.com

    💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.

  • Hi Ramzi,

    Thank you. I got the formula for ($) metrics.

    However the Count how many are booked I don't get it. I will explain further my concern.

    This is how my column looks like below:

    Date Received | Name of Person | Purchased Amt | STATUS (Sorry I wrote BOOKED) in the beginning.

    My STATUS column is PENDING AND BOOKED.

    Now, I want to count how many BOOKED Status are there per month.

    Can you write me a formula? BTW thanks I used your column sections.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!