COUNTIFS

Marilen.Navarro103391

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:

日期收到过| |名字的人购买了Amt | Booked

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

Thanks.

Paul Newcome

You do not need the MONTH helper column in your materials sheets. You can use a COUNTIFS along the lines of...

=COUNTIFS({1st Sheet Name Column}, "Mary", {1st Sheet Status Column}, "Booked",{1st Sheet Date Column}, AND(IFERROR(MONTH(@cell), 0) = ##, IFERROR(YEAR(@cell), 0) = ####))

The above looks at the month and year to make sure your metrics don't pull Jan 2019 and Jan 2020 into the same count.

The same concept can be used for your SUMIFS.

=SUMIFS({1st Sheet Column to Sum}, {1st Sheet Name Column}, "Mary", {1st Sheet Status Column}, "Booked", {1st Sheet Date Column}, AND(IFERROR(MONTH(@cell), 0) = ##, IFERROR(YEAR(@cell), 0) = ####))

You don't necessarily need to restructure anything or add columns.

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]$3refers 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

Marilen.Navarro103391

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.

Paul Newcome

You do not need the MONTH helper column in your materials sheets. You can use a COUNTIFS along the lines of...

=COUNTIFS({1st Sheet Name Column}, "Mary", {1st Sheet Status Column}, "Booked",{1st Sheet Date Column}, AND(IFERROR(MONTH(@cell), 0) = ##, IFERROR(YEAR(@cell), 0) = ####))

The above looks at the month and year to make sure your metrics don't pull Jan 2019 and Jan 2020 into the same count.

The same concept can be used for your SUMIFS.

=SUMIFS({1st Sheet Column to Sum}, {1st Sheet Name Column}, "Mary", {1st Sheet Status Column}, "Booked", {1st Sheet Date Column}, AND(IFERROR(MONTH(@cell), 0) = ##, IFERROR(YEAR(@cell), 0) = ####))

You don't necessarily need to restructure anything or add columns.