Hi,

can you help me to calculate two working experiences in excel.

Ex: 12 Yeas 3 Months 5 Days - CDB Finance company

9 Years 4 Months 7 Days - Commercial Bank

These are two working experiences in two working places of one person. how to sum these two experiences as a total experience in excel. Please help me.

Hi all,

What if one would like to make a date range non-mandatory?

Meaning, let's say that I have two cells with dates in them that are used as my beginning and end date threshold, but when those cells aren't populated, the sum for all dates is pulled from the data set.

My formula (below) has two match criteria with the date ranges at the back of the formula. What I'm trying to do is only filter the sum by the date range if a condition is met (ideally a cell that acts as select Yes or No on "Do you want to limit the sum to a specific date range?"

=SUMIFS(BURN_DATA!$S:$S,BURN_DATA!$R:$R,$A12,BURN_DATA!$H:$H,I$3,BURN_DATA!O:O,">="&I9,BURN_DATA!O:O,"<="&J9)


THANKS IN ADVANCE!

Aloha, I'm trying to figure out how to sum another column of units, when one column of dates is sequential. Example: I have rows with dates, Mar 1, Mar 2, mar3, then marc 6,7, then march 10.
I need to sum the corresponding units in of another column for mar 1-3, mar 6-7 and mar 10.

I am trying to create a holiday calendar to calculate the following information:

Start Date:
End Date (if there is one, otherwise calculate on the entire holiday year_
No of Holidays
Holiday year runs from 01/04 to 31/03 with an entitlement of 20 days plus bank holidays

any help is much appreciated

Hello there,
I am a sales man at hotel and I want to create a rate plan for different dates and room types,
for example
10.08.19 rate 50 usd per person,
11.08.19 rate 45 usd per person
12.08.19 rate 55 usd per person
13.08.19 rate 60 usd per person
14.08.19 rate 60 usd per person

when guest ask me 2 different room types for example family room =pp rate x 3
and standard room =pp rate x 2

check in 10.08 and for 5 nights.. On rate plan I filled all dates rates, but I cannot arrange for room types..

could you help someone to me pls..

I'm trying to sum the monthly data, in column E, between the Date in Q4 and R4, using the dates in column A. All I get is 0 for an answer. Am I making a mistake or is there a bug in Excel?

=SUMIFS('Major business figures'!$E:$E,'Major business figures'!$A:$A,">"&Analysis!Q$4,'Major business figures'!$A:$A,"<="&Analysis!R$4)

Hello Excel gurus,

quick question. I`m trying to sum amounts that are in date range but all i`m trying is useless. The task is the following:

I have column with trade amounts (more then 1 hundred lines, means different trades). Each trade have booking date and Maturity date. First trade was done on 27.04.2017 and last one 21.11.2018. So, I need to sum trades amounts for each and every date separately starting from 27.04.2017 and till 21.11.2018. How to type the formula correctly having mentioned conditions on top?

Appreciated in your help.

Thansk

hai Friends I have one dout, how to calculate how many dates between one date to another date.

does the date format matter?
The formula when i type the 5th July as 5/7/2018, the formula is picking up 7th May instead.

Hi there,


I want to use a IF formula to pick up the number in a cell, only if the dates in which it rates to fall between two dates. Eg if the start date is between the two dates, then pick up a number from the cell.



Help

Very helpful, thanks!

If I wanted to incorporate another IF into this formula how would I do it? My formula looks like this:

=SUMIFS(E2:E51,C2:C51,">="&C52,C2:C51,"<="&AH52)
I also want to include a variable in CELL D52 from the range of D2:D51.

=SUMIFS(Expenses!D6:K300,Expenses!C6:C300,"> ="&References!B5,Expenses!C6:C300,"< ="&References!C5)

returns #VALUE. Where am I going wrong? Expense tab, column C has dates. Expenses tab columns D thru K have the $$ amounts that need to be summed. References tab B5 has beginning date. References tab C5 has ending date.

DATED wgt
09-05-2017 0.71
30-05-2017 0.55
07-06-2017 0.53
07-06-2017 0.50
07-06-2017 0.36
07-06-2017 1.01
08-06-2017 0.80
13-06-2017 0.51
13-06-2017 0.50
13-06-2017 1.02
14-06-2017 3.01
15-06-2017 1.00
15-06-2017 0.71
19-06-2017 0.54
24-06-2017 1.40
26-06-2017 1.61
27-06-2017 2.53
30-06-2017 0.70
04-07-2017 0.30
04-07-2017 0.30
04-07-2017 1.30
04-07-2017 0.50
05-07-2017 0.61
10-07-2017 1.01
10-07-2017 0.60
10-07-2017 0.70
12-07-2017 0.50

i want to calculate date wise weight and convert to month



Total month
May -
June -
July -

thanks bro it's working

No idea why the formula is not working :( I am using it exactly. When I copy the sample data it works but, when I put in my data it fails. I never had an issue with sumifs ...weird Here is my formula....=SUMIFS('KPI Data'!$G:$G,'KPI Data'!$E:$E,">="&$C$4,'KPI Data'!$E:$E,"

Thanks a bunch. Works perfectly well.

I want the formula for exactly reverse criteria.. I want to arrive a value of a date.. I have from and to dates and value..

hi i want to apply this formula on other sheet while my values and date are on other sheet how could this be possible please

Hi,

I require a formula which is kind of the reverse of the =SUMIFS(B2:B8,A 2:A8,">="&E2,A2 :A8,"

Hi,

I need a formula that will display a total if the start and finish dates are between dates shown in a row.

I have tried various IF( AND...) formulas but get caught out on either the start or finish dates

Good day,

Info to note
* working with 10 products (Prices vary every day) prices in worksheet 1-"Prices" (Colum A dates) (Products Row 2C-M) and quantity sold for the day in sheet 2 "Book 34" (each book consist of 100 invoices (1-100 in row B dates in A) products in row 2 (C-M) as well.
* should I add a product the formula needs to identify with a formula not with an amount

Example. I need to type the date and the quantity in each product and the total formula will identify with the product and date and multiply together.

Please assits

I am successful using the above formula ( =SUMIFS(B2:B8,A2:A8,">="&E2,A2:A8,"

Need formula for subtotal of "A" within date range. (total $ collected from "A" between 4/1 and 4/6, total $ collected from "B" between 4/1 and 4/16)

I have used your formula, but it only works if you type in the date, it is very static. If I make it look at a cell, it doesn't sum. What am I doing wrong.=SUMIFS(B2:B8,A2:A8,">=e4
",A2:A8,"

If you need a reference to another book/worksheet, use something like CONCATENATE(">=",'GLOBAL REPORT'!E4) as the criteria.Forsome reason this is needed.

Which formula would you use should you wish to add certain cell values should the date be within 10 days of the current date of the spreadsheet. I.E I have two date cells, 1 being an automatic date updater when spreadsheet is opened and another date cell being that of which when an entry was made. There is over 100 lines of this and would like to add just the sales within 10 days of current date.

i have worksheet 1 with customer list and date of order.
On second work sheet i have a weekly sales report with start and end range.

i would like to look up date of order and summarize sales by week if the date range falls with the start and and end range on workbook 2. I've tried combining a sumifs, vlookup with a statement and can not resolve the calculation errors.

How do you do this when the date range is defined in another column?

E.g. I have a main worksheet with a "week ending" column and another with expenses entered on a daily basis. How can I find and sum all expenses on or up to 7 days before each week ending date?

=SUMIF(expenses!A2:expenses!A1024,main!$A$2&"