Formula

[Sarahh2831]

Hi 

I want to use sumifs as I multiple criteria 

tab 1 

Jan-23 <need total positive value >
jan-23 <need total negative value>

Feb-23 <need total positive values>
feb-23 <need total negative values>

tab 2 

9/1/2023  -45
2/1/2023   46

1/2/2023  -45

1/2/2023  46

how do if use the sumifs function to summarise the data in tab 2 to reflect the summary in tab 1

please help 

[#2]

@Sarahh2831 

You can use the SUMIFS function in Excel to summarize the data in Tab 2 based on multiple criteria and display the results in Tab 1.

Here’s an example of how you can do this:

Let’s assume that your data in Tab 2 is organized as follows:

• Column A contains the dates
• Column B contains the values

In Tab 1, you can use the following formula to calculate the total positive values for January 2023:

=SUMIFS(Tab2!B:B, Tab2!A:A, ">="&DATE(2023,1,1), Tab2!A:A, "<"&DATE(2023,2,1), Tab2!B:B, ">0")

This formula sums the values in column B of Tab 2 where the corresponding date in column A is within January 2023 and the value in column B is greater than 0.

Similarly, you can use the following formula to calculate the total negative values for January 2023:

=SUMIFS(Tab2!B:B, Tab2!A:A, ">="&DATE(2023,1,1), Tab2!A:A, "<"&DATE(2023,2,1), Tab2!B:B, "<0")

This formula is similar to the previous one but it sums only the values that are less than 0.

You can use similar formulas to calculate the total positive and negative values for February 2023 or any other month. Just change the dates in the criteria to match the desired month.

I hope this helps! 

[#3]

Hi Nic

Thank you so much, but can I use the arrayformula to covert column A , tab 2 convert to month

[#4]

How could the formula look then. I don’t want to adjust the formula as the months and years change

[#5 · Accepted solution]

@Sarahh2831 

You can make the formula more flexible by using cell references instead of hard-coded dates. For example, you can enter the start and end dates for the desired month in two cells in Tab 1, let’s say cells C1 and D1. Then you can modify the formula to reference these cells instead of using hard-coded dates.

Here’s an example of how the modified formula for calculating the total positive values for the month specified in cells C1 and D1 would look like:

=SUMIFS(Tab2!B:B, Tab2!A:A, ">="&C1, Tab2!A:A, "<"&D1, Tab2!B:B, ">0")

Similarly, you can modify the formula for calculating the total negative values for the month specified in cells C1 and D1 as follows:

=SUMIFS(Tab2!B:B, Tab2!A:A, ">="&C1, Tab2!A:A, "<"&D1, Tab2!B:B, "<0")

With these modified formulas, you can easily change the month for which you want to calculate the total positive and negative values by simply changing the dates in cells C1 and D1.

I wish you a nice day and much success with Excel!

[#6]

Hi there

my data is set out like this

Tab 1

fee type 1 Dollar value
client 2
Client-1 2

fee type 2 Dollar value
client 2
client-1 3

fee type 3 Dollar value
client 2
client-1 3



tab 2 - summary tab

fee type Total Dollar value
client X
client -1. X


In tab 2, If I use sumif, but I need to exclude fee type 2 and 3 from the sumif function as that is not required . How would the formula look

could you please assist

[#7]

@Sarahh2831 

Assuming the fee types are listed in column A, the dollar values are listed in column B, and the client names are listed in column C, you can use the SUMIFS function in tab 2 to exclude fee type 2 and 3 from the total.

Here's how the formula would look like:

=SUMIFS('Tab 1'!B:B,'Tab 1'!C:C,C2,'Tab 1'!A:A,"Fee type 1")

In this formula, C2 is the cell that contains the client name in tab 2, and "Fee type 1" is the fee type that you want to include in the total. You can modify the formula to include additional fee types as needed.

This formula will only sum the dollar values for fee type 1 and for the specific client name in cell C2. The SUMIFS function allows you to specify multiple criteria to filter the data, so you can exclude fee types 2 and 3 by simply not including them in the formula.

One alternative formula would be to use the SUMPRODUCT function with two conditions:

=SUMPRODUCT(('Tab 1'!C:C=C2)*('Tab 1'!A:A="Fee type 1"),'Tab 1'!B:B)

In this formula, 'Tab 1'!C:C=C2 checks if the client name in 'Tab 1'!C:C matches the client name in cell C2, and 'Tab 1'!A:A="Fee type 1" checks if the fee type in 'Tab 1'!A:A matches "Fee type 1". The * operator multiplies the two conditions together, resulting in an array of 1's and 0's, where 1 represents a row that meets both conditions.

Finally, the SUMPRODUCT function multiplies this array with the dollar values in 'Tab 1'!B:B, and returns the sum of the products, effectively summing only the dollar values for fee type 1 and for the specific client name in cell C2.

Note that this formula assumes that all the data in 'Tab 1'!A:B is numeric, and there are no blank cells or text values. If there are text values or blanks in the data, you may need to modify the formula with additional conditions to exclude those rows.

[#8]

Hi Nic, trying to convert 23/5/2023 in days is 23 . Do you have a formula you could share ?

[#9]

@Sarahh2831 

With a date in D2, the formula =DAY(D2) will return the day number.