help in formula countif OR sumproduct

[Malik Nadeem]

Dear Sir,

i want to know the formula how to count value of B column 20, 40, 40hc &
40flt which value in C clumn is starting from zero and grader then zero
(except minuse value)

example

a b c d
12348 20 0 almar
23456 40 2 jv
98745 40hc -2 jv
25896 40flt 10 rsl
12348 20 -1 almar
23456 40 9 cim
98745 40hc -2 rsl
25896 40flt 13 rsl
12348 20 0 rsl
23456 40 2 cim
98745 40hc -2 almar
25896 40flt 10 cim

i need the result in following shap

20 40 40HC 40FLT
ALMAR 01 00 00 00
JV 00 01 00 00
CIM 01 02 00 01
RSL 00 00 00 02

TOTAL
20 40 40hc 40flt
02 03 00 03

i need the formula like countif or sumproduct

also i need conditional formula in above result how many cntrs are coverd in
0-5 days and how many covered in 6-10 days and how many after 10 days like
below

===========<<0-5 days>>====== =========<<6-10days>>==========
========<<more then 10days>>=========
20 40 40HC 40FLT 20 40 40HC 40FLT 20 40 40HC 40FLT
ALMAR 01 00 00 00
JV 00 01 00 00
CIM 01 02 00 01
RSL 00 00 00 02

thanks in advance for support and help

regards
Malik

 

[Per Jessen]

Hello

Sample data in A213
In F2:F5 and down you have "almar" etc.
In G1:J1 20, 40 etc.

Enter this formula in G2 and copy it to fill the table.

=SUMPRODUCT(--($B$2:$B$13=G$1),--($D$2:$D$13=$F2),--($C$2:$C$13>=0))

Re: Last question, I can not see how you want to split the data.

Regards,
Per

 

[Shane Devenshire]

Hi,

This would be a nice time to consider a pivot table, with the column D's
data in the Row area, column B's data in the Column area, and column A's data
in the Data area with the calculation set to Count. You must have one row of
titles in your data set, which you haven't shown us.

As far as the breakout of your data by 0-5, 6-10 and so on, you can't do
that without some data which indicates the age of the items and you don't
show any such fields in your raw data.

 

[Malik Nadeem]

in the last i want to split data slap wise like slap one starting from zero
and ending 5 (2nd slap starting from 6 and ending 10) slap third starting
from 11 and on an on 999999999

FYI column C is the days

in given formula will be working like this
=SUMPRODUCT(--($B$2:$B$13=G$1),--($D$2:$D$13=$F2),--($C$2:$C$13>=0))

in the last array i.e. ($C$2:$C$13>=0))

it should be like this ($C$2:$C$13{{ equal to zero and less then +6 or
between -1 to +6))
in a 2nd slap ($C$2:$C$13{{ equal to +6 and less then +12 or between +5 to
+12))
in a 3rd slap ($C$2:$C$13>=12))

hope clear

 

[Per Jessen]

No problem, just add an extra criterium for column C:

=SUMPRODUCT(--($B$2:$B$13=G$1),--($D$2:$D$13=$F2),--($C$2:$C$13>=0),--($C$2:$C$13<6))

=SUMPRODUCT(--($B$2:$B$13=M$1),--($D$2:$D$13=$F2),--($C$2:$C$13>=6),--($C$2:$C$13<12))

=SUMPRODUCT(--($B$2:$B$13=S$1),--($D$2:$D$13=$F2),--($C$2:$C$13>=12))

Regards,
Per