SUMIF Job Entries Over Date Range + Ignore Duplicate Job Entries

[sansai]

Hello,

I am trying to SUM the total amount of requests my department receives in any given period of time (say, all of February). I have some duplicate entries (for example, see attached: A5, A7, & A8), but I only need the SUM of the first instance of the number requested (Column C). So in the case of the A5, A7, and A8 I should only see 10 requested, and not 30.

Some of the Job Names (Column A) have duplicate entries because a user did not finish the entire request the first time, in this case the system creates a duplicate entry with the remaining number.


Attached is a snapshot example of part of the data I am using. If for some reason more is required, let me know and I will populate the sheet with more data; I am simply including what I think is most necessary. I keep my data as its own tab in excel, and run my reports and formula in other tabs. So it leaves some room to work with.

Thanks in advance for any tips on how to calculate!

[cbatrody]

Hi

Please try the below array formula:

Please Login or Register  to view this content.

to be confirmed by pressing CTRL+SHIFT+ENTER

See the attached file.

[sansai]

That is a great start, thank you!

However, I am wanting to SUM the number in Column C. So by what you posted, February should read 82 # Requested. Can I modify that code to SUMIF instead of COUNTIF?

[cbatrody]

I managed to achieve this with the help of 2 helper columns, see the attached file.

In E2:

Please Login or Register  to view this content.

In F2:

Please Login or Register  to view this content.

In J2:

Please Login or Register  to view this content.

I'll try to come up with a formula without the helper columns in a while and update it here.

[sansai]

That works great! I was about to start making an array lookup for this based on a different structure, but this is much cleaner.

Kudos your way!