# Counting with sumproduct

#### cleetus_snow

##### New Member
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?

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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

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]

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.

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?

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.

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
CDEF
1010
2000
3011
4010
5110
6001
7111
8000
9256
1022+3=55+1=6
Sheet2

Exactly!

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

cleetus_snow said:
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
ABCDE
1
20111
30001
40111
50101
61101
7001
81111
90000
10
112567
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.

cleetus_snow said:
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!

Replies
14
Views
579
Replies
3
Views
356
Replies
4
Views
290
Replies
3
Views
92
Replies
3
Views
198

1,196,229
Messages
6,014,129
Members
441,804
Latest member
oifaejw

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back