Jululian Excel

  • Thread starter Thread starter George A. Jululian
  • Start date Start date
G

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
 
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
 
Good Morning Dear Sir,
i wrote the following

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

please advice

Regards
 
=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
 
You are genius

Many thanks teacher



Jacob Skaria said:
=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
Click to expand...
 
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
 
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
 
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
 
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
 
No Sir did work the formula




Jacob Skaria said:
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
Click to expand...
 
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
 
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
 
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
Click to expand...
then i need the result for these only (FLEET) and not the all table result

please help
 
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
 
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
 
Back
Top