# Formula to calculate total of a series of data based on other factors

1. ## Formula to calculate total of a series of data based on other factors

Hi All,

I have been trying to solve this problem now for a while and could do with some guidance.

My sheet contains two lists of users and two sets of values and I need my formula to be able to calculate one of the values (C2, D2) if there name appears in E2 and another if in F2.

On top of all of that, I need it to only search within a specific date range that is specified in A2 onwards.

This will generate a report that is split by month.

I hope I have explained myself sufficiently!

I have attached a copy of the basic sheet with some test data for you to maybe better understand.

Any help would be massively appreciated.

2. ## Re: The impossible formula (I hope not!) :confused:

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

3. ## Re: The impossible formula (I hope not!) :confused:

... and when you change the thread title, please amend your sheet to show some expected results - your explanation isn't clear enough.

4. ## Re: Formula to calculate total of a series of data based on other factors

Hi Guys,

Sorry about that! I apologise for my ignorance of the forum rules.

I'm very much struggling to explain my issue and therefore I wasn't sure of how to title it.

I have tried to explain more clearly below:

The user will complete rows A to G with data from an external source.

I want a formula that completes the following actions:

• Checks Column A for date ranges that match the month of the year required.
• Just looking at the month required it should check Column E and G for a users name and calculates the total values in the same row from Column D or F.
• The results should be displayed on the "Reports" Sheet under the users name and month of the year

I have attached an updated version with hopefully a clearer explanation.

Thanks!

5. ## Re: Formula to calculate total of a series of data based on other factors

One way:

=SUMIFS(Form!\$D:\$D,Form!\$A:\$A,">="&Report!C\$1,Form!\$A:\$A,"<="&EOMONTH(Report!C\$1,0),Form!\$E:\$E,Report!\$A2)+SUMIFS(Form!\$F:\$F,Form!\$A:\$A,">="&Report!C\$1,Form!\$A:\$A,"<="&EOMONTH(Report!C\$1,0),Form!\$G:\$G,Report!\$A2)

6. ## Re: Formula to calculate total of a series of data based on other factors

That seems to have done the trick!

Thank you so much for your help

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1