Sumif only returns first value

Question

Hi

Hoping someone can help me with this problem!

I am trying to prepare a new cash flow model, this will be prepared on a daily, weekly and monthly level therefore 3 separate tabs.  The weekly and monthly tabs will be driven from the daily sheet, hopefully totalling all specific line items for a specific date range.  My summary data example is below:

Therefore for the weekly summary I would like to total all Income (ref 1.1.1) for the date range 01/3/15 - 07/03/15

For the monthly summary exactly the same only for a larger date range 01/03/15 - 31/3/15.

I have tried sumif(b:b,"1.1.1",d2:j2) however this only returns the first value (100), what do I need to change in this formula to total all items or (100 + 200 + 300 .....  2800)?

Can someone please recommend a formula or method to enable this?

Thank you for your help!

Chris

Type	Ref	Description	1/03/2015	2/03/2015	3/03/2015	4/03/2015	5/03/2015	6/03/2015	7/03/2015
Income	1.1.1	Income 1	$100.00	$200.00	$300.00	$400.00	$500.00	$600.00	$700.00
Income	1.1.2	Income 2	$100.00	$200.00	$300.00	$400.00	$500.00	$600.00	$700.00
Income	1.1.3	Income 3	$100.00	$200.00	$300.00	$400.00	$500.00	$600.00	$700.00
Income	1.1.4	Income 4	$100.00	$200.00	$300.00	$400.00	$500.00	$600.00	$700.00
Income	1.1.5	Income 5	$100.00	$200.00	$300.00	$400.00	$500.00	$600.00	$700.00
Income	1.1.6	Income 6	$100.00	$200.00	$300.00	$400.00	$500.00	$600.00	$700.00
Income	1.9.0	TOTAL	$400.00	$1,200.00	$1,800.00	$2,400.00	$3,000.00	$3,600.00	$4,200.00
Expenses	2.1.1	Expenses 1	$50.00	$50.00	$50.00	$50.00	$50.00	$50.00	$50.00
Expenses	2.1.2	Expenses 2	$40.00	$40.00	$40.00	$40.00	$40.00	$40.00	$40.00
Expenses	2.1.3	Expenses 3	$30.00	$30.00	$30.00	$30.00	$30.00	$30.00	$30.00
Expenses	2.1.4	Expenses 4	$20.00	$20.00	$20.00	$20.00	$20.00	$20.00	$20.00
Expenses	2.9.0	TOTAL	$140.00	$140.00	$140.00	$140.00	$140.00	$140.00	$140.00
Total			$260.00	$1,060.00	$1,660.00	$2,260.00	$2,860.00	$3,460.00	$4,060.00

Answer 1

Hi try this

=SUMPRODUCT(--(B:B="1.1.1")*(D2:J2))

Answer 2

If each row is going to be unique in column B and you are summing only one row then

=Sum(D2:J2)  for the first week in the weekly summary for the 1.1.1 row.

the second week would be

=Sum(K2:Q2)   and so forth. 

assuming it looks the same as this table, then you would drag fill down the column. 

With a fixed table, I am not sure why you would need any type of dynamic/conditional addressing.

--

Regards,

Tom Ogilvy

Answer 3

ediardp,

If I had the first table of data below, your formula  would return 14 but it seems I would expect the correct answer to be 28.

Header1	Header2	Header3	Header4	Header5	Header6	Header7	Header8	Header9	Header10
A2	1.1.1	C2	1	1	1	1	1	1	1
A3	1.1.2	C3	2	2	2	2	2	2	2
A4	1.1.1	C4	3	3	3	3	3	3	3
A5	1.1.4	C5	4	4	4	4	4	4	4

or if I had this data

Header1	Header2	Header3	Header4	Header5	Header6	Header7	Header8	Header9	Header10
A2	1.1.0	C2	1	1	1	1	1	1	1
A3	1.1.2	C3	2	2	2	2	2	2	2
A4	1.1.1	C4	3	3	3	3	3	3	3
A5	1.1.4	C5	4	4	4	4	4	4	4

I would expect an answer of 21 but your formula would return 7.

Any thoughts?

--

Regards,

Tom Ogilvy

Answer 4

your welcome, thanks for the feedback

Answer 5

Hi Ediardp

Thanks for that it works great

Cheers!