Counting with sumproduct

[cleetus_snow]

I have seen many great examples of counting using sumproduct. Each has counted based on an AND condition. Can sumproduct be used to count based on an OR condition?

 

[Zack Barresse]

Hello, welcome to the board!

Of course it can. When you use mathematical operators in a situation like this, you can use the * operator as your AND, and your + operator as your OR. For an example, post some data. Also, have a look here:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[cleetus_snow]

Thanks for the link firefytr. I have bookmarked it for future reference.

My data is something like this:

C1 C2 C3
0 1 0
0 0 0
0 1 1
0 1 0
1 1 0
0 0 1
1 1 1
0 0 0

2 5 6

I am trying to calculate a running total (count) for each column. Each new instance of "1" in any column should increase the running total. What do you think?[/img]

 

[Zack Barresse]

Hi, I'm really failing to see where you would apply any type of SUMPRODUCT formula. You're just looking for running totals?? If so, just use SUM..

=SUM(A:A)

Assuming your data is in column A, it will ignore text. Or you could just use the COUNT function. SUMPRODUCT is used for counting/summing/processing with multiple conditions. If you have more, you should post them.

 

[cleetus_snow]

Perhaps sumproduct isn't what I need, but I don't know if sum or count is either.

Looking down C1, there are 2 instances of "1", count =2. Looking down C1 & C2 together, there are 3 new instances of "1" and 2 previous instances of "1", count = 5. Looking down C1, C2 & C3 together, there is 1 new instance of "1" and 5 previous instances of "1", count = 6. The counts are across the bottom in my example.

Does that explanation help any?

 

[Zack Barresse]

No, doesn't actually help me much. I think I'm confused about where your data is housed exactly. Can you give a specific cell address range where your data is housed? Also, what is it exactly you are looking to come up with that you do not already have?

If it would be easier to post with the HTML Maker, you can find a link at the bottom of the page and instructions in the Board Announcements forum.

 

[babycody]

Can't help, but I do understand what you want. You want to add to the total in the previous column the rows in the current column that have 1 entered into that row for the first time. When you get to the next column you wish to do the same using 1s that have been entered into the row for the first time.

Book6
	C	D	E	F
1	0	1	0
2	0	0	0
3	0	1	1
4	0	1	0
5	1	1	0
6	0	0	1
7	1	1	1
8	0	0	0
9	2	5	6
10	2	2+3=5	5+1=6
Sheet2

 

[cleetus_snow]

Exactly!

Thanks for the html insert. I was just working on adding that myself.

 

[Aladin Akyurek]

Thanks for the link firefytr. I have bookmarked it for future reference.

My data is something like this:

C1 C2 C3
0 1 0
0 0 0
0 1 1
0 1 0
1 1 0
0 0 1
1 1 1
0 0 0

2 5 6

I am trying to calculate a running total (count) for each column. Each new instance of "1" in any column should increase the running total. What do you think?[/img]

One way...

Book1
	A	B	C	D	E
1
2	0	1	1	1
3	0	0	0	1
4	0	1	1	1
5	0	1	0	1
6	1	1	0	1
7	0	0	1
8	1	1	1	1
9	0	0	0	0
10
11	2	5	6	7
12
13
Sheet1

A11:

=COUNTDIFF(IF($A$2:A9=1,ROW($A$2:A9)-ROW($A$2)+1,""),FALSE,"")

which is confirmed with control+shift+enter then copied across.

For COUNTDIFF you need the latest version of the morefunc.xll add-in which is free.

 

[Domenic]

Thanks for the link firefytr. I have bookmarked it for future reference.

My data is something like this:

C1 C2 C3
0 1 0
0 0 0
0 1 1
0 1 0
1 1 0
0 0 1
1 1 1
0 0 0

2 5 6

I am trying to calculate a running total (count) for each column. Each new instance of "1" in any column should increase the running total. What do you think?[/img]

Assuming that A1:C8 contains your data, try...

A10, copied across:

=SUMPRODUCT(--(COUNTIF(OFFSET($A$1:A8,ROW($A$1:A8)-MIN(ROW($A$1:A8)),0,1),1)>0))

or

=SUM(--(MMULT(--($A$1:A8=1),TRANSPOSE(COLUMN($A$1:A8)^0))>0))

...confirmed with CONTROL+SHIFT+ENTER,not just ENTER. Adjust the range accordingly.

Hope this helps!