Jululian Excel

[George A. Jululian]

Hi Dear All,

i have tabe like below
B C D E F G
Date Retail Fleet Retail Fleet Retail Fleet
01-Jan-09
02-Jan-09
03-Jan-09

i need formula to sum only the fleet sales

please help

regards

 

[Jacob Skaria]

Hi George

The below formula will sum the fleet entries for the month specified in cell
A2.

=SUMPRODUCT((TEXT(A2:A100,"mmyyyy")=TEXT(A2,"mmyyyy"))*(B1:G1="Fleet"),B2:G100)


You can take out the first condition if you want a full sum of fleets
=SUMPRODUCT((B1:G1="Fleet")*B2:G100)

If this post helps click Yes

 

[George A. Jululian]

Good Morning Dear Sir,
i wrote the following

=SUMPRODUCT(("Statistics!B5:BW5),*Statistics!B6:BW370"))
the result #VALUE!

please advice

Regards

 

[Jacob Skaria]

=SUMPRODUCT((Statistics!B5:BW5="fleet")*Statistics!B6:BW370)

Didnt you notice the second formula I suggested
=SUMPRODUCT((B1:G1="Fleet")*B2:G100)


If this post helps click Yes

 

[George A. Jululian]

You are genius

Many thanks teacher

=SUMPRODUCT((Statistics!B5:BW5="fleet")*Statistics!B6:BW370)

Didnt you notice the second formula I suggested
=SUMPRODUCT((B1:G1="Fleet")*B2:G100)


If this post helps click Yes

 

[Jacob Skaria]

Welcome and thanks for the feedback..

If this post helps click Yes

 

[George A. Jululian]

Dear Sir,

i amend the formula to read

=IF(ISNA(MATCH(1,(Stock!$A$3:$A$35=Statistics!B1)*
(Stock!$B$3:$B$35=Statistics!B2)
(Stock!$C$3:$C$35=Statistics!B3),0)),"",SUMPRODUCT((Statistics!B5:BW5="Fleet")*Statistics!B6:BW370))

and the result it gave me the all sales value and not my condition

please help

 

[Jacob Skaria]

I tried this...(Array entered) and gives the correct result.

If a record exists in Sheet 'Stock' matching the criterias of B1,B2,B3 then
return the total of fleet

If this post helps click Yes

 

[George A. Jululian]

Sir,

The record exists in Sheet 'Stock' matching the criterias of B1,B2,B3 waht i
need is only the total fleet for b1,b2,b3

and second why when i enter the formula it request to upadte the link
and i know there is no links

regards

 

[Jacob Skaria]

Try the same formula in a new workbook with 2 sheets named as Stock and
Statistics..and with some dummy data..

If this post helps click Yes

 

[George A. Jululian]

No Sir did work the formula

Try the same formula in a new workbook with 2 sheets named as Stock and
Statistics..and with some dummy data..

If this post helps click Yes

 

[George A. Jululian]

Dear Sir

=IF(AND('Daily Report'!F9=Statistics!B1,'Daily
Report'!G9=Statistics!B2,'Daily
Report'!H9=Statistics!B3),SUMPRODUCT((Statistics!B5:BW5="Fleet")*Statistics!B6:BW370," "))

i need the result from Fleet column which in column c,or e,g and so on

please help

 

[Jacob Skaria]

Syntax error .Try the below

=IF(AND('Daily Report'!F9=Statistics!B1,'Daily Report'!G9=Statistics!B2,
'Daily Report'!H9=Statistics!B3),
SUMPRODUCT((Statistics!B5:BW5="Fleet")*Statistics!B6:BW370),"")

If this post helps click Yes

 

[George A. Jululian]

I am very sorry that cause you headack on this issue

the result form the formula is 12

and should read 1

because need is if IF(AND('Daily Report'!F9=Statistics!B1,'Daily
Report'!G9=Statistics!B2,

'Daily Report'!H9=Statistics!B3) is ture

then i need the result for these only (FLEET) and not the all table result

please help

 

[Jacob Skaria]

Interesting. Try the below test in a new sheet.

Col A Col B Col C
other fleet other
1 2 3
4 5 6
7 8 9
10 11 12

=SUMPRODUCT((A1:C1="Fleet")*A2:C5)

'That is the same thing mentioned in the other formula..

If this post helps click Yes

 

[George A. Jululian]

Good morning Sir,

A B C D E F
Orange Orange Banana Banana Tomato Tomato
Other Fleet Other Fleet Other Fleet
1 2 33 1 8 1
4 5 6 5 4 9
7 8 9 8 35 5
10 11 12 9 66 4

the formula is to find in the table the sum of Banana fleet

please help

Regards