Hi
I have a report with data on the first worksheet. I need to calculate the total in worksheet 2 based on the criteria of site in column A and shown in the helper column 2 (Create date). The total should be for the current month of may.
Hi
I have a report with data on the first worksheet. I need to calculate the total in worksheet 2 based on the criteria of site in column A and shown in the helper column 2 (Create date). The total should be for the current month of may.
So we are looking at "Completion Date" not "Create Date"?
We are looking at create date.
.. And Helper1 and Helper 2 are the same date: why do we need the helper data?
None of the "Create dates" are in May so your expected results are wrong (if you do mean created in May).
Sry for the confusion. Yes we can use helper 1 for the create date.
I have readjusted the data as attached.
Try
in C3
=SUMPRODUCT((worksheet1!$A$2:$A$100=$B3)*(MONTH(worksheet1!$B$2:$B$100)=5))
Copy down
Change 5 to required month number
..and make sure "Site" is consistent : "Site1" vs "Site 1"
Hi, It worked. Can I check if I need to add an additonal criteria in this formula to not include "cancelled" conditions in column O, where would i add this? There are total of 3 conditions (Open/Closed/Cancelled)
Try
=SUMPRODUCT((worksheet1!$A$2:$A$100=$B3)*(MONTH(worksheet1!$B$2:$B$100)=5)*(worksheet1!$O$2:$O$100<>"Cancelled"))
and I suggest you make column O entries using Data Validation LIST with Open, Closed, Cancelled
Thanks John. That helps.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks