I have a table on a dashboard report that uses sumproduct to pull multiple criteria to get the results. I want to be able to switch the results by using a drop down box that has 3 options - (1) Number of Units; (2) Total Charge; (3) Variable Costs. The lookup table (on a different tab) has Units in Column D, Charge in Column E and Variable costs in column G. What formula would I use in order to have the correct column's results based on the drop down selection? Thanks.

]]>WS attached

I need a formula that will sum a column based on a date range entered into 2 cells based on the adjacent date by each entry.

Formula entered into Q6 (will also be needed for R6 through V6 based on adjacent info)

When a date range is entered into O6 & P6, formula will SUM E19:E1018 based on the dates in B19:B1018 that will fall between the range entered.

I need a formula that will sum a column based on a date range entered into 2 cells based on the adjacent date by each entry.

Formula entered into Q6 (will also be needed for R6 through V6 based on adjacent info)

When a date range is entered into O6 & P6, formula will SUM E19:E1018 based on the dates in B19:B1018 that will fall between the range entered.

Hi,

I got a sheet where I paste times in 08:55 format that later I need to sum. But it doesnt sum even if I field format it to time. I believe the real problem behind is that the value of the cells where time is stored is 08:00 rather than 0.452. But how do I convert the times into the correct format without any additional columns? Please see the attachment for example.

https://www.excelforum.com/attachmen...1&d=1490290599

]]>I got a sheet where I paste times in 08:55 format that later I need to sum. But it doesnt sum even if I field format it to time. I believe the real problem behind is that the value of the cells where time is stored is 08:00 rather than 0.452. But how do I convert the times into the correct format without any additional columns? Please see the attachment for example.

https://www.excelforum.com/attachmen...1&d=1490290599

Hello Experts,

I am trying to figure out an Excel formula that will accomplish the following. I am trying to count occurrences based on 2 columns/ranges worth of data and 3 criteria. I'll write it out as a mathematical expression:

(Range1 = "Y)" AND ((Range2 = "Phase4") OR (Range2 = "Transitioned on*"))

So, I am trying to count the number of instances where elements in Range 1 = "Y" and elements in Range2 = "Phase4" OR "Transitioned on*". Please note the wildcard.

Any help coming up with this formula would be appreciated. Thank you.

TMc

]]>I am trying to figure out an Excel formula that will accomplish the following. I am trying to count occurrences based on 2 columns/ranges worth of data and 3 criteria. I'll write it out as a mathematical expression:

(Range1 = "Y)" AND ((Range2 = "Phase4") OR (Range2 = "Transitioned on*"))

So, I am trying to count the number of instances where elements in Range 1 = "Y" and elements in Range2 = "Phase4" OR "Transitioned on*". Please note the wildcard.

Any help coming up with this formula would be appreciated. Thank you.

TMc

Hi,

I'm trying to sum - well a SUMIF - of a column - if the criteria allows - but some cells in the column contain text, which I want to disregard. It's throwing errors as it's seeing the text and can't parse.

Screen Shot.pdf

What I want to do is count Column H -*Number of Days Spent at Level* - only if Column B - *Level *- contains 'Level 201'

So trying to sum the number of says spent at Level 201 for across all users.

I'm stuck between 2 formulas - that I need to somehow join together

=SUMIF(B8:B64,"Level 201",H8:H64)

=SUM(IF(ISNUMBER(VALUE(LEFT(H8:H64,2))),VALUE(LEFT(H8:H64,2)),VALUE(LEFT(H8:H64,1)))) - This is throwing the error as it gets stuck and doesn't recognise the letters Da - though it should be disregarding them?

Can anyone help?

Aisling

]]>I'm trying to sum - well a SUMIF - of a column - if the criteria allows - but some cells in the column contain text, which I want to disregard. It's throwing errors as it's seeing the text and can't parse.

Screen Shot.pdf

What I want to do is count Column H -

So trying to sum the number of says spent at Level 201 for across all users.

I'm stuck between 2 formulas - that I need to somehow join together

=SUMIF(B8:B64,"Level 201",H8:H64)

=SUM(IF(ISNUMBER(VALUE(LEFT(H8:H64,2))),VALUE(LEFT(H8:H64,2)),VALUE(LEFT(H8:H64,1)))) - This is throwing the error as it gets stuck and doesn't recognise the letters Da - though it should be disregarding them?

Can anyone help?

Aisling

Good afternoon. I'm stumped on trying to setup a formula that moves with a changing reference and multiple criteria. For example, I have a file that measures labor productivity by pay periods, which I have numbered, and locations. I'm using a drop down for Period numbers and want to use that as a static reference for a lookup. For instance, if I choose Pay Period 10, I want it to include Pay Period 10 and the preceding 9 periods to sum up. When Pay Period 11 hits, I want to be able to change the drop down to Pay Period 11 and have the formula automatically sum that pay period and the preceding 9. I was trying different iterations of SUMIFS, MATCH and OFFSET. Nothing seems to work.

I hope that makes sense.

]]>I hope that makes sense.

Hi,

Please would you mind advising on the correct formula for the attached please? I just want to bring back the total of 2 cols based on a department criteria. I can't get my sum if to work...

Many thanks

Suzanne

Please would you mind advising on the correct formula for the attached please? I just want to bring back the total of 2 cols based on a department criteria. I can't get my sum if to work...

Many thanks

Suzanne

Hi,

I have a spreadsheet with =sumproduct. The formula works fine and looks like this:

=SUMPRODUCT((A1:A500=Elsa)*(B1:B500=Anna)*E1:E500)

FOr this I have two questions - I just cannot make it work:

1: This is a basic, ofcourse, but anyway: How do i tell Excel to look in ALL cells in a column, not just - as I have specified - numbers 1-500?

2: Column C in the spreadsheet is a Dates column. Is it possible to add another variable into the formula, namely "cells in column C, that contain an instance of one specific month"? If we use March as an example, I would like the formula to sum the total number of appearances in column E of "Elsa+Anna during March". Date format is yyyy-mm-dd.

Thankful for all suggestions :)

]]>I have a spreadsheet with =sumproduct. The formula works fine and looks like this:

=SUMPRODUCT((A1:A500=Elsa)*(B1:B500=Anna)*E1:E500)

FOr this I have two questions - I just cannot make it work:

1: This is a basic, ofcourse, but anyway: How do i tell Excel to look in ALL cells in a column, not just - as I have specified - numbers 1-500?

2: Column C in the spreadsheet is a Dates column. Is it possible to add another variable into the formula, namely "cells in column C, that contain an instance of one specific month"? If we use March as an example, I would like the formula to sum the total number of appearances in column E of "Elsa+Anna during March". Date format is yyyy-mm-dd.

Thankful for all suggestions :)

Hi folks,

I'm sure there is a very simple solution to this issue.

See the attached sample spreadsheet.

In cell F3 I am attempting to retrieve a count of cells that have todays date. Trouble is the date field is actually date & time. I do not want to seperate the date from the time into two columns.

How do i modify the query to return the correct number?

I'm sure there is a very simple solution to this issue.

See the attached sample spreadsheet.

In cell F3 I am attempting to retrieve a count of cells that have todays date. Trouble is the date field is actually date & time. I do not want to seperate the date from the time into two columns.

How do i modify the query to return the correct number?

I need help with an IF(AND) Function. Here's my Formula: =IF(AND(C8>=0,C9>=0),Just Do It,No Way)

Cell C8 and C9 both have a 0 in them. I've tried changing the values in C8 & C9 to .5 but still get the same result.

I get a #NAME? Return. What did i do wrong?

HI,

Greetings!!!

I was working on a report and was trying to sort a column containing data like "1 Day 2 hours 3 minutes"..

but when i try to sort i am getting like below,

__Actual time left__

1 Day 2 Hours 14 Minutes

1 Day 4 Hours 50 Minutes

1 Day 4 Hours 59 Minutes

1 Day 5 Hours 23 Minutes

1 Day 5 Hours 29 Minutes

1 Day 5 Hours 49 Minutes

1 Day 5 Hours 59 Minutes

1 Day 6 Hours 18 Minutes

1 Day 6 Hours 20 Minutes

1 Day 6 Hours 31 Minutes

1 Day 6 Hours 38 Minutes

1 Day 6 Hours 49 Minutes

1 Day 7 Hours 11 Minutes

1 Day 7 Hours 2 Minutes

1 Day 7 Hours 30 Minutes

1 Day 7 Hours 57 Minutes

1 Day 7 Hours 6 Minutes

10 Hours 1 Minutes

10 Hours 12 Minutes

10 Hours 22 Minutes

11 Hours 18 Minutes

11 Hours 5 Minutes

11 Hours 50 Minutes

12 Hours 16 Minutes

12 Hours 2 Minutes

12 Hours 27 Minutes

12 Hours 30 Minutes

12 Hours 51 Minutes

14 Hours

14 Hours 14 Minutes

14 Hours 20 Minutes

14 Hours 24 Minutes

14 Hours 38 Minutes

14 Hours 43 Minutes

14 Hours 49 Minutes

14 Hours 5 Minutes

14 Hours 53 Minutes

the above date is a result which i get after sorting from "Sort A to Z" which is not sorted in correct order.

I spent almost half day in doing this but i can't

Kindly guide me in sorting it in correct format.

]]>Greetings!!!

I was working on a report and was trying to sort a column containing data like "1 Day 2 hours 3 minutes"..

but when i try to sort i am getting like below,

1 Day 2 Hours 14 Minutes

1 Day 4 Hours 50 Minutes

1 Day 4 Hours 59 Minutes

1 Day 5 Hours 23 Minutes

1 Day 5 Hours 29 Minutes

1 Day 5 Hours 49 Minutes

1 Day 5 Hours 59 Minutes

1 Day 6 Hours 18 Minutes

1 Day 6 Hours 20 Minutes

1 Day 6 Hours 31 Minutes

1 Day 6 Hours 38 Minutes

1 Day 6 Hours 49 Minutes

1 Day 7 Hours 11 Minutes

1 Day 7 Hours 2 Minutes

1 Day 7 Hours 30 Minutes

1 Day 7 Hours 57 Minutes

1 Day 7 Hours 6 Minutes

10 Hours 1 Minutes

10 Hours 12 Minutes

10 Hours 22 Minutes

11 Hours 18 Minutes

11 Hours 5 Minutes

11 Hours 50 Minutes

12 Hours 16 Minutes

12 Hours 2 Minutes

12 Hours 27 Minutes

12 Hours 30 Minutes

12 Hours 51 Minutes

14 Hours

14 Hours 14 Minutes

14 Hours 20 Minutes

14 Hours 24 Minutes

14 Hours 38 Minutes

14 Hours 43 Minutes

14 Hours 49 Minutes

14 Hours 5 Minutes

14 Hours 53 Minutes

the above date is a result which i get after sorting from "Sort A to Z" which is not sorted in correct order.

I spent almost half day in doing this but i can't

Kindly guide me in sorting it in correct format.

Hi everyone,

I am so close to solving this formula but I can't figure it out. Please help!

Please see the attached sample excel

What I want to happen is that when you filter "YEAR" for either 2016 or 2017, I want Table 2. to automatically calculate the percentage of YES=1 from column "Y/N" but ONLY OUT OF the total responses for the specified site indicated in Table 2.

So for example, if you filter YEAR for 2017, in Table 2. the percentage for site TWH should show up as 50% b/c there is only one 1 out of 2 responses for TWH.

Similarly, for site BAR, in Table 2 the percentage should be 60%.

However, what is happening, is that the formula in Table 2. is calculating the number of Yes=1 for each site out of ALL the responses in the Y/N column.

I hope this make sense. Someone please help I am so damn close!

I am so close to solving this formula but I can't figure it out. Please help!

Please see the attached sample excel

What I want to happen is that when you filter "YEAR" for either 2016 or 2017, I want Table 2. to automatically calculate the percentage of YES=1 from column "Y/N" but ONLY OUT OF the total responses for the specified site indicated in Table 2.

So for example, if you filter YEAR for 2017, in Table 2. the percentage for site TWH should show up as 50% b/c there is only one 1 out of 2 responses for TWH.

Similarly, for site BAR, in Table 2 the percentage should be 60%.

However, what is happening, is that the formula in Table 2. is calculating the number of Yes=1 for each site out of ALL the responses in the Y/N column.

I hope this make sense. Someone please help I am so damn close!

Hello there, I am new to this forum and was hoping someone could shed some light on the slight confusing situation I currently find myself in.

I have been tasked with creating an easy to use pricing system for an upcoming show allowing quick and accurate quotations for customers. I have created several drop down menus to correspond with the many options available. What I would like to do, if its possible, is to create a button in the spreadsheet that will reset the information within my drop down lists to the "No Selection Made" choice thus resetting all costs.

Is this possible? If so, how would I go about making this a reality?

Thanks in advance.

]]>I have been tasked with creating an easy to use pricing system for an upcoming show allowing quick and accurate quotations for customers. I have created several drop down menus to correspond with the many options available. What I would like to do, if its possible, is to create a button in the spreadsheet that will reset the information within my drop down lists to the "No Selection Made" choice thus resetting all costs.

Is this possible? If so, how would I go about making this a reality?

Thanks in advance.

Hello !

So i'm going a little bit mad with this thing I'm really no expert I hope i'll be able to catch out more now that I've registered to the forum :P

i want to achieve this result :

Capture.PNG

I have the values in "data" & "output"

what i want to achieve is that when somebody changes the dropdown to one of the values, which i read form the "data" values, the cell on the right of the combobox get's updated with the corresponding "output" line.

So IE if i set 1 on the combo box the cell should become 2 if i choose 2 the cell needs to update to 4 and so on.

the result i get is just the relative index, if i select "data" 1 i get 1, when i get to 2 i get 2 and so on.

how can i resolve this problem ?

I've read that =indirect($ROW$COLLUMN) should work but i can't get the desired result.

Thank you for your help !!

]]>So i'm going a little bit mad with this thing I'm really no expert I hope i'll be able to catch out more now that I've registered to the forum :P

i want to achieve this result :

Capture.PNG

I have the values in "data" & "output"

what i want to achieve is that when somebody changes the dropdown to one of the values, which i read form the "data" values, the cell on the right of the combobox get's updated with the corresponding "output" line.

So IE if i set 1 on the combo box the cell should become 2 if i choose 2 the cell needs to update to 4 and so on.

the result i get is just the relative index, if i select "data" 1 i get 1, when i get to 2 i get 2 and so on.

how can i resolve this problem ?

I've read that =indirect($ROW$COLLUMN) should work but i can't get the desired result.

Thank you for your help !!

So lets say for example I have a formulas like so in a column:

1 * 1.1

3 * 0.5

4 * 0.5

5 * 0.5 etc.

Then I solve the whole column with a macro to get these results below.

1.10

1.50

2.00

2.50 etc.

If I change it to no decimal place I get

1

2

2

3 etc.

Here is my problem, I want their actual values to become

1

2

2

3 etc. so when I change back to 2 decimal places their actual values are now

1.00

2.00

2.00

3.00 etc. and not their original values of:

1.10

1.50

2.00

2.50 etc.

I would like it that I can select the whole column and change values to round up if its 1st decimal value is lets say 1.5 and up to become the value of 2 and if its 1.4 and below, it rounds down to 1 as well. Can anyone help me with that? I would really appreciate it.

]]>1 * 1.1

3 * 0.5

4 * 0.5

5 * 0.5 etc.

Then I solve the whole column with a macro to get these results below.

1.10

1.50

2.00

2.50 etc.

If I change it to no decimal place I get

1

2

2

3 etc.

Here is my problem, I want their actual values to become

1

2

2

3 etc. so when I change back to 2 decimal places their actual values are now

1.00

2.00

2.00

3.00 etc. and not their original values of:

1.10

1.50

2.00

2.50 etc.

I would like it that I can select the whole column and change values to round up if its 1st decimal value is lets say 1.5 and up to become the value of 2 and if its 1.4 and below, it rounds down to 1 as well. Can anyone help me with that? I would really appreciate it.