Counting

[vecia]

Hello, my data looks something like this:

Product Stage Campaign Batch
1564 1 1 101
1564 1 1 101
1564 1 1 102
1564 1 1 103
1564 1 1 103
1564 1 1 103
1564 1 1 104

There are three cells on the spreadsheet (H1, H2, H3) where the user
can type in the product, stage and campaign that they are interested in
(there are many variations, I have shown only one for convenience and
these may not always be numeric).

The formulas I need are

1) A count of how many batches of a particular product, stage and
campaign there were (for this example, this should = 4)

2) How many batches had only one entry (for this example = 2)

I can get part of the way with SUMPRODUCT but now I am stuck - please
help.

 

[Leo Heuser]

I have assumed, that the "Batch"-column values are
always numeric, and that your data is in A28 (A11 headings)
One way to do it:

Q1:

=SUMPRODUCT((FREQUENCY((A2:A8=H1)*(B2:B8=H2)*
(C2:C8=H3)*D28,(A2:A8=H1)*(B2:B8=H2)*(C2:C8=H3)*D28)>0)+0)-
(FREQUENCY((A2:A8=H1)*(B2:B8=H2)*(C2:C8=H3),0)>0)

Q2:

=SUMPRODUCT((FREQUENCY(D28,D28)=1)+0)

--
Best Regards
Leo Heuser
Excel MVP

Followup to newsgroup only please.

 

[vecia]

Thanks Leo,

I have got the first one to work.


I added another criterion in (E2:E8<>"") and it still worked but I went
wrong when I tried combining it with the second formula.


This is what I entered:


=SUMPRODUCT((FREQUENCY((A2:A8=H1)*(B2:B8=H2)*
(C2:C8=H3)*(E2:E8<>"")*D28,(A2:A8=H1)*(B2:B8=H2)*(C2:C8=H3)*(E2:E8<>"")*D28)=1)+0)-
(FREQUENCY((A2:A8=H1)*(B2:B8=H2)*(C2:C8=H3)*(E2:E8<>""),0)>0)

This works fine most of the time but sometmes gives an answer of -1.

Please will you help me out again?

 

[vecia]

Thanks Leo,

I have got the first one to work.


I added another criterion in (E2:E8<>"") and it still worked but I went
wrong when I tried combining it with the second formula.


This is what I entered:


=SUMPRODUCT((FREQUENCY((A2:A8=H1)*(B2:B8=H2)*
(C2:C8=H3)*(E2:E8<>"")*D28,(A2:A8=H1)*(B2:B8=H2)*(C2:C8=H3)*(E2:E8<>"")*D28)=1)+0)-
(FREQUENCY((A2:A8=H1)*(B2:B8=H2)*(C2:C8=H3)*(E2:E8<>""),0)>0)

This works fine most of the time but sometmes gives an answer of -1.

Please will you help me out again?

 

[vecia]

Thanks Leo,

I have got the first one to work.


I added another criterion in (E2:E8<>"") and it still worked but I went
wrong when I tried combining it with the second formula.


This is what I entered:


=SUMPRODUCT((FREQUENCY((A2:A8=H1)*(B2:B8=H2)*
(C2:C8=H3)*(E2:E8<>"")*D28,(A2:A8=H1)*(B2:B8=H2)*(C2:C8=H3)*(E2:E8<>"")*D28)=1)+0)-
(FREQUENCY((A2:A8=H1)*(B2:B8=H2)*(C2:C8=H3)*(E2:E8<>""),0)>0)

This works fine most of the time but sometmes gives an answer of -1.

Please will you help me out again?

 

[vecia]

Thanks Leo,

I have got the first one to work.


I added another criterion in (E2:E8<>"") and it still worked but I went
wrong when I tried combining it with the second formula.


This is what I entered:


=SUMPRODUCT((FREQUENCY((A2:A8=H1)*(B2:B8=H2)*
(C2:C8=H3)*(E2:E8<>"")*D28,(A2:A8=H1)*(B2:B8=H2)*(C2:C8=H3)*(E2:E8<>"")*D28)=1)+0)-
(FREQUENCY((A2:A8=H1)*(B2:B8=H2)*(C2:C8=H3)*(E2:E8<>""),0)>0)

This works fine most of the time but sometmes gives an answer of -1.

Please will you help me out again?

 

[Peo Sjoblom]

Maybe it is time to stop pressing that send button now?

--

Regards,

Peo Sjoblom

 

[Leo Heuser]

And counting <g>

My formula said
D28)>0
not
D28)=1

--
Best Regards
Leo Heuser
Excel MVP

Followup to newsgroup only please.

 

[vecia]

Thanks Leo,

I have got the first one to work.


I added another criterion in (E2:E8<>"") and it still
worked but I went
wrong when I tried combining it with the second formula.


This is what I entered:


=SUMPRODUCT((FREQUENCY((A2:A8=H1)*(B2:B8=H2)*
(C2:C8=H3)*(E2:E8<>"")*D28,(A2:A8=H1)*(B2:B8=H2)*(C2:C8=H3)*(E2:E8<>"")*D2
8)=1)+0)-
(FREQUENCY((A2:A8=H1)*(B2:B8=H2)*(C2:C8=H3)*(E2:E8<>""),0)>0)

This works fine most of the time but sometmes gives an
answer of -1.

Also, I just wanted to check - should I be entering these as array
formulas or not, they seem to give the same result either way but I
just wanted to be sure.

Please will you help me out again?