How to create formula in worksheet using c#?

Question

1.80/5 (2 votes)

See more:

I have used Epplus library for excel worksheet.
Now i have work sheets , i have columns name,marks,etc in worksheet1 ,in that which student has <35 marks , i fill that cell with red colour.

My requirements are

1) in worksheet2 , i want total rows of worksheet1 (total student )
i want to create formula of worksheet1 into worksheet2.
i have used this formula ,

worksheet2.Cells[10, 5].Formula = "SUM('" + worksheet1.name+ "'." + worksheet1.Cells[4, 6].Address + ":" + worksheet1.Cells[rowIndex, 6].Address + ")"

but its gives ERR:508 in worksheet2 at cell[10,5].

2) count of red cell , eg.
student name :- xyz
marks :- 70 , 34 , 34
so here 2 cell will be in red color.
so i want count of red cell in worksheet2.

is there any solution reply as soon as possible.

Posted 7-Apr-14 19:55pm

Bhagyashree Chaudhari - Member 10473634

Updated 29-Apr-14 21:12pm

v2

Add a Solution

Comments

Maciej Los 8-Apr-14 2:11am

Not clear!
Please, post sample input data and expected output.

Bhagyashree Chaudhari - Member 10473634 8-Apr-14 2:26am

how to add screen shot here?
can u tell me
Thank you

Bhagyashree Chaudhari - Member 10473634 8-Apr-14 2:43am

Worksheet1 :-
Marks
No Name Sub1 Sub2 Sub3
1 ABC 70 34 34 (here sub2's and sub3's cell will be in red colour)
2 XYZ 80 30 90 (here sub2's cell will be in red colour)

now in worksheet2

Fail in Sub1 0
Fail in Sub2 2
Fail in Sub3 1

using Epplus Library.

j snooze 8-Apr-14 17:54pm

It sounds like you have the first portion figured out(red coloring)... I think for worksheet2, you may be looking for something like this formula
=COUNTIF(Sheet1!C2:C3,"<35")
where the first parameter is the cell range, and the second parameter the "<35", is the criteria. It should only count the cells less than 35 then.

Bhagyashree Chaudhari - Member 10473634 9-Apr-14 3:21am

Thankx for response.
i want to do this , by colour index.
eg. where i got cell has red color , count only those cell
so here instead of <35 , i want to pass colour index.

3 solutions

Solution 1

Excel doesn't support color index. You will have to use "Countif"

Som

Posted 23-May-14 16:45pm

Som Shekhar

Solution 3

Have a look here: Count The Number of Cells With Specific Cell Color By Using VBA[^]
How to count and sum cells based on background color in Excel?[^]
Tip: Easily COUNT or SUM cells based on their Color[^]

Posted 27-Jul-14 20:49pm

Maciej Los

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Bhagyashree Chaudhari - Member 10473634 · Accepted Answer · 2014-07-27T19:41:00

Solution 2

In this , i have done count row by row ...
Like when i gets red column , in NEW COLUMN i will write 0 or ERROR else i will write 1 or CORRECT.
then in other work sheet i will count NEW COLUMN's where CORRECT is occurs.

Posted 27-Jul-14 19:41pm

Bhagyashree Chaudhari - Member 10473634